Varying columns in the SELECT statement pgAdmin

Question

I am creating a function that will be used for a search bar and to filter. Below is my code:

CREATE OR REPLACE FUNCTION functions.search_function(
    student text[] DEFAULT NULL,
    professors text[] DEFAULT NULL)
    RETURNS TABLE(
    student text,
    student_id int,
    professor_name text,
    subject text,
    subject_time timestamp without time zone,
    room_number int,
    building text)
LANGUAGE 'sql'
AS $BODY$
    SELECT 
    student,
    student_id,
    professor_name,
    subject,
    subject_timetime,
    room_number,
    building
    FROM "school_records_table"
    WHERE (LOWER("student") = any($1) OR $1 IS NULL)
    AND ((LOWER("professor_name") =any($2)) OR $2 IS NULL)
$BODY$;

Is it possible to insert anywhere in the code to vary the columns in the SELECT statement? For example, I want to SELECT student, professor_name, subject and room_number only? It will be used as a filter. If yes, how can I insert it in my function? Thank you very much

Belayer · Answer 1 · 2021-12-07T04:16:34.257

With static SQL you cannot vary the number of columns, the data type of the columns, nor, in this case, the name of the returned columns. But your function is returning a table. So use the returned table as such:

select student
     , professor_name
     , subject
     , room_number  
  from functions.search_function( <student array>
                                , <professors array> 
                                );

Varying columns in the SELECT statement pgAdmin

1 Answers1