0

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.

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;

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.

  • 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. – zakaria mouqcit Oct 23 '17 at 8:13
  • I'm not sure why any of that matters. You can still supply an ID given my calling syntax. – Evan Carroll Oct 23 '17 at 16:15
  • I solved the problem in an other way.Thanks for getting involved with me to solve my issue – zakaria mouqcit Oct 23 '17 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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