0

I created a dynamic SQL function for the first time and wondering what is the correct way to call the function? Here's the code

CREATE OR REPLACE FUNCTION functions.search_function(_columns text)
    RETURNS TABLE(student text,
    student_id text,
    professor_name text,
    subject text,
    subject_time text,
    room_number text,
    building text) 
  LANGUAGE plpgsql AS
$func$
DECLARE
   _columns text := 'student_id::text, subject_timetime::text, building::text';  
BEGIN
   RETURN QUERY EXECUTE '
    SELECT 
    student,
    professor_name,
    subject,
    room_number,
    ' || _columns || '
    FROM "school_records_table"  '
   USING  _columns;
END
$func$;

I have been receiving an error when I try this

SELECT functions.searchandfilter2_function(
    ('students') )
driedbananas
  • 9
  • 4
  • Can you show an error message? – Sharofiddin Dec 07 '21 at 07:11
  • Try this : `SELECT functions.searchandfilter2_function('students')` – Edouard Dec 07 '21 at 07:13
  • it says the column does not exist – driedbananas Dec 07 '21 at 07:16
  • RETURN QUERY EXECUTE ' SELECT student, professor_name, subject, room_number, $1 FROM "school_records_table" ' USING _columns; – Edouard Dec 07 '21 at 07:17
  • The function makes no sense to me. You are using a dynamic list of columns in the SELECT, but your function returns a static list of result columns. So why the dynamic SQL at all? But you can't pass column names with the `USING` clause - that's only used for passing _values_ (not identifiers) –  Dec 07 '21 at 08:22
  • Most probably not the reason for the error, but functions that return a table, should be put into the from clause: `select * from functions.searchandfilter2_function(...)` –  Dec 07 '21 at 08:22
  • thank you for this! needed someone to explain what's going on with the this is just my first time trying to create one – driedbananas Dec 07 '21 at 08:32
  • @a_horse_with_no_name how can I pass column names? – driedbananas Dec 07 '21 at 09:11
  • @driedbananas: You can't. Only in dynamic SQL. To get started: https://stackoverflow.com/a/18386034/939860 Remember to disclose your Postgres version and the verbatim error message in your next question. – Erwin Brandstetter Dec 07 '21 at 13:10

0 Answers0