Call dynamically a function using plpgsql

Question

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.

Answer 1

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.

• zakaria mouqcit: Okay, This function would be called in an other one in a where clause. As previously explained the result of the function is a text which depends on a given id passed on parameters, yet what I want to do is call it dynamically, it means that the id won't have a known value (id=30) for instance, but any given id by the user , the function should search for it and return the team which match it.
• Evan Carroll: I'm not sure why any of that matters. You can still supply an ID given my calling syntax.
• zakaria mouqcit: I solved the problem in an other way.Thanks for getting involved with me to solve my issue