1

I create a function that returns the value of a field when the id is given :

CREATE OR REPLACE FUNCTION get_dcteam(id int) RETURNS text
AS $$
#print_strict_params on
DECLARE
dc_team text;
BEGIN
    SELECT monitoring_table.dc_team INTO STRICT dc_team
        FROM monitoring_table WHERE monitoring_table.id = get_dcteam.id;
    RETURN dc_team;
END
$$ LANGUAGE plpgsql;

I call it this way :

select * from get_dcteam((select id from monitoring_table where id=30))

What I want to do is call the function dynamically. I don't want to precise a known id.
How can I do this?

0

Don't ever call it like that. Because it returns a single value (RETURNS text) Call it like this instead, it's cleaner.

SELECT id, get_dcteam(id)
FROM monitoring_table
WHERE id = 30;

Now if you want it for more id's remove the WHERE Clause.

SELECT id, get_dcteam(id)
FROM monitoring_table;

Also you should probably rewrite that to use regular SQL, as it may be inlined.

CREATE OR REPLACE FUNCTION get_dcteam(id int)
RETURNS text
AS $$
    SELECT monitoring_table.dc_team
    FROM monitoring_table
    WHERE monitoring_table.id = get_dcteam.id;
$$ LANGUAGE sql
IMMUTABLE;

But one step further, I'd just delete the function entirely. It's basic SQL and it's just going to slow you down. Look at what you're doing: get_dcteam is querying monitoring_table, and you're calling it on a query that is already visiting that table! This will be much faster and clearer:

SELECT id, dc_team
FROM monitoring_table
WHERE id = 30;
|improve this answer|||||
  • thanks for your answer. This call will give me several rows. What I Want is use this function in a where clause of an other one, but without passing the id in parameters. – zakaria mouqcit Oct 20 '17 at 17:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.