CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
JR Galia
JR Galia

Reputation: 17269

Return ResultSet in pgsql function

I have the function below...

CREATE OR REPLACE FUNCTION class_listing(var_sem integer, var_sy character) RETURNS SETOF RECORD AS
DECLARE
    current_offering record;

BEGIN
    SELECT subcode, offerno INTO current_offering FROM offering WHERE SY=var_sem AND SEM=var_sy;

END;

How to return current_offering as resultset?

Upvotes: 0

Views: 113

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45770

You can use a SQL or PLpgSQL functions. Using anonymous records as returning type is not practical (mainly it is not friendly, when you write queries). Use RETURNS TABLE or OUT parameters instead.

CREATE OR REPLACE FUNCTION class_listing(var_sem integer, var_sy varchar)
RETURNS TABLE (subcode varchar, offerno int) AS $$
BEGIN
  RETURN QUERY SELECT o.subcode, o.offerno
                  FROM offering 
                 WHERE SY=var_sem AND SEM=var_sy;
END;
$$ LANGUAGE plpgsql;

or SQL language

CREATE OR REPLACE FUNCTION class_listing(var_sem integer, var_sy varchar)
RETURNS TABLE (subcode varchar, offerno int) AS $$
  SELECT o.subcode, o.offerno
                  FROM offering 
                 WHERE SY=$1 AND SEM=$2;
$$ LANGUAGE sql;

Attentions - query based functions works (with small exception) as optimizer barrier. So be careful when you use it in complex queries.

For completeness - your example can be written as:

CREATE OR REPLACE FUNCTION class_listing(var_sem integer, var_sy varchar)
RETURNS SETOF RECORD AS $$
DECLARE current_offering record;
BEGIN
  FOR current_offering IN 
          SELECT o.subcode, o.offerno
             FROM offering 
            WHERE SY=var_sem AND SEM=var_sy;
  LOOP
    RETURN NEXT current_offering;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

But this form is deprecated now

Upvotes: 2

Related Questions