CODEWITHSUNDEEP
postgresqlplpgsql
Dilyor Toshboltayev
Dilyor Toshboltayev

Reputation: 15

return table in postgresql two case

I am migrating stored procedure from SQL SERVER to POSTGRESQL. I should return table. But, I have following problem:

IF ROWSTATE = 'L' THEN
      return TABLE A;
ELSIF ROWSTATE = 'C' THEN
      return TABLE B;
END IF

How to do it, using function in POSTGRESQL?

Upvotes: 1

Views: 108

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

You have to create a function that RETURNS TABLE with the columns expected from tables a and b, e.g:

CREATE OR REPLACE FUNCTION myfunc(rowstate text)
RETURNS TABLE (ret_col1 text, ret_col2 text) AS $$ 
BEGIN
  IF $1='L' THEN
    RETURN QUERY SELECT cola1, cola2 FROM a;
  ELSEIF $1='C' THEN
    RETURN QUERY SELECT colb1, colb2 FROM b;
  END IF;
END;
$$ LANGUAGE plpgsql;

Demo: db<>fiddle

Upvotes: 1

Related Questions