Varying columns in the SELECT statement pgAdmin-CodePudding

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

CodePudding user response：

You can switch which columns are being selected with a simple case statement.

On mobile so cannot format well currently, but you can assign your column to a value based ok whatever your determinating factors are.

This article may help: How can I SELECT multiple columns within a CASE WHEN on SQL Server?

CodePudding user response：

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> 
                                );