CODEWITHSUNDEEP
postgresql
SezKo
SezKo

Reputation: 47

Postgres function returning arguments or zero value

I want to create a stored function. I want to return columns value or zero value when a select query executed if data found. But I get syntax error. How can I solve this problem?

The error is

ERROR: syntax error at or near "do" LINE 6: do $$

My code is:

CREATE OR REPLACE FUNCTION tuvimer.getProjectTypes(
   OUT id integer,
   OUT name character varying)
RETURNS SETOF record AS
do $$
IF EXISTS (select t.id, t.name from tuvimer.tuvi_project_category t) THEN
   RETURN t.id, t.name;
 ELSE
  RETURN 0;
 END IF;
$$
LANGUAGE plpgsql

Upvotes: 0

Views: 1849

Answers (1)

user330315
user330315

Reputation:

The do is for anonymous PL/pgSQL blocks, not for functions. In PL/PgSQL you need begin ... end (in fact that is needed inside a DO block as well)

But that would only fix the obvious syntax error regarding the incorrect DO usage, but it still won't work for several reasons:

You can't just return t.id, t.name as at that point in the code you have not assigned values to that - in fact the alias t does not exist outside of the select statement.

The exists check does not seem to make any sense. It checks if there is at least one row in the table, if there is none you are trying to some random values from that table - doesn't make sense

You also can return just a scalar value (0) from a function defined to return a result with two columns. You need to return at least one row with two columns.

If I had do guess: you are trying to return a "dummy row" if nothing is found. If so you could adapt this example from the manual

CREATE OR REPLACE FUNCTION tuvimer.getProjectTypes(
   OUT id integer,
   OUT name character varying)
RETURNS SETOF record AS
$$
BEGIN --<< The BEGIN goes into the body

 return query select t.id, t.name from tuvimer.tuvi_project_category t;

 IF NOT FOUND THEN
    -- return a dummy row
    return query select 0, null::character varying;
 END IF;

END; --<< And you need an END here
$$
LANGUAGE plpgsql

Unrelated, but: I prefer the syntax returns table (id integer, name varchar) over returns setof record and the definition of the columns as OUT parameters.

Upvotes: 4

Related Questions