PostgreSQL error in creating function with varying columns using RECORD

Question

I have this function that should be returning a varying columns, can I get a help on what is wrong with my code?

CREATE OR REPLACE FUNCTION functions.search(column_name VARCHAR(40)) 
RETURNS SETOF RECORD AS $$
DECLARE   
    rec RECORD;
BEGIN
    RETURN QUERY EXECUTE format('SELECT %I FROM students_table);
END;
$$ LANGUAGE plpgsql;

Call:

SELECT * FROM functions.search(
    ('Student Id', 'Subect')
) as ("Student Id" bigint, "Subect" text)

Here's my error

ERROR: function functions.search(record) does not exist LINE 1: SELECT * FROM functions.search( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 15

Thank you very much

Your function is declared to receive a single string, not a list of column names. So you need to call it with `search('"Student Id", "Subject"')` - but then the `%I` placeholder won't work, as it expects a single identifier, not multiple. To be honest: the whole function doesn't really make sense to me. What problem are you trying to solve with that? Why can't you just write `select "Student Id", "Subject" from students_table` ? — , Dec 13 '21 at 08:27
@a_horse_with_no_name how do I make it multiple? i need to input in the parameters a list of column names thank you very much — driedbananas, Dec 13 '21 at 08:28
@a_horse_with_no_name the requirement given to us is to make function that you can input varying columns — driedbananas, Dec 13 '21 at 08:30

Laurenz Albe · Answer 1 · 2021-12-13T10:15:53.913

0

There are several things wrong here.

First, to support a variable number of arguments, you need a VARIADIC parameters. Then, your format function call is somewhat truncated and syntactical nonsense.

You could experiment with a function like this:

CREATE FUNCTION functions.search(VARIADIC column_names text[]) RETURNS SETOF record
    LANGUAGE plpgsql AS
$$BEGIN
    RETURN QUERY EXECUTE
        format('SELECT %s FROM students_table',
               (SELECT string_agg(quote_ident(s), ', ')
                FROM unnest(column_names) AS cols(s))
              );
END;$$;

You can call it like this:

SELECT *
FROM functions.search('Student Id', 'Subect')
   AS ("Student Id" bigint, "Subect" text);

edited Dec 13 '21 at 10:15

answered Dec 13 '21 at 09:15

Laurenz Albe

209,280
17
206
263

I tried this but I am receiving an error ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function search(text[]) line 2 at RETURN QUERY SQL state: 0A000 – driedbananas Dec 13 '21 at 09:48
I used this SELECT search( (array['Student Id', 'Subect']::text[]) ) – driedbananas Dec 13 '21 at 09:49
this error showed up again when I try two values in the parameter ERROR: function functions.search(record) does not exist LINE 1: SELECT * FROM functions.search( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 15 – driedbananas Dec 13 '21 at 10:25
Then you did something different than what is shown in my answer. – Laurenz Albe Dec 13 '21 at 10:55

PostgreSQL error in creating function with varying columns using RECORD

1 Answers1