Call dynamically a function using plpgsql

plpgsql

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(given value in parameter) but any existing value in my table. The result of this query I will use it in a trigger.
I'm a newbie and I'm struggling to learn dynamic SQL.
Your help would be precious.
Best regards.

Best Answer

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;

Having previously answered this you said

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 3 hours ago

I'm not even sure what that means. Perhaps you could explain.

Related Question