CODEWITHSUNDEEP
postgresql
SDK
SDK

Reputation: 1

How to return select query in pl pgsql

While running a Select query returning all columns in Pl Pgsql written in a function, during passing the arguments error is shown.

CREATE OR REPLACE FUNCTION myfunc(ENROLL VARCHAR(50)) RETURNS  SETOF RECORD AS $$
DECLARE CALTYPE VARCHAR(50);
BEGIN
SELECT COMPLETED INTO 
CALTYPE FROM TEMPDATA WHERE
ROLL=ENROLL;
    IF CALTYPE='FALSE' THEN
        RETURN QUERY
        SELECT * FROM SEM8
        WHERE ROLL=ENROLL;
    ELSE
        RETURN QUERY
        SELECT * FROM TEMPDATA
        WHERE ROLL=ENROLL;
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM myfunc('123');

Upvotes: 0

Views: 335

Answers (1)

user1760150
user1760150

Reputation: 654

Functions returning SETOF RECORD need a column definition. You can define it at run time like this:

SELECT * FROM myfunc('123') mf(columnA TEXT, columnB INTEGER);

or you can change the return type of the function to TABLE and define it there like this:

CREATE OR REPLACE FUNCTION myfunc(ENROLL VARCHAR(50)) 
RETURNS  TABLE (
    columnA TEXT,
    columnB INTEGER
) AS $$
DECLARE CALTYPE VARCHAR(50);
BEGIN
SELECT COMPLETED INTO 
CALTYPE FROM TEMPDATA WHERE
ROLL=ENROLL;
    IF CALTYPE='FALSE' THEN
        RETURN QUERY
        SELECT * FROM SEM8
        WHERE ROLL=ENROLL;
    ELSE
        RETURN QUERY
        SELECT * FROM TEMPDATA
        WHERE ROLL=ENROLL;
    END IF;
END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions