Get dynamically value of a column using plpgsql [closed]

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.
How can I do this?

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;