Returning boolean from SQL function

Question

I'm trying to write a PL/pgSQL function in postgres that returns a boolean expression. Currently the code looks like this:

CREATE OR REPLACE FUNCTION is_visible_to(role integer, from_role integer)
  RETURNS boolean
  LANGUAGE SQL STABLE STRICT AS $$
    RETURN $1 = $2 OR first_predicate($1, $2) OR second_predicate($2, $1);
 $$;

The functions first_predicate and second_predicate exist and return booleans, but I'm not getting much help looking at them because they are defined with something like

SELECT COUNT(*) > 0 FROM ... WHERE ...

I am getting the following syntax error:

ERROR:  syntax error at or near "RETURN"
LINE 4:     RETURN $1 = $2 first_predicate($1, $2) OR second_predicate(...
            ^

So obviously I'm misunderstanding something rather fundamental here. I'm sort of new to the whole PL/pgSQL thing, so links to a good tutorial or two would also be appreciated.

The project I'm working on uses postgres exclusively, and already contains a lot of postgres specific code, so solutions don't have to be portable to other databases.

Edit: Fixed it myself with

CREATE FUNCTION is_visible_to (role integer, from_role integer)
  RETURNS BOOLEAN AS '
    DECLARE
        role ALIAS FOR $1;
        from_role ALIAS FOR $2;
    BEGIN
      RETURN (role = from_role) OR
             first_predicate(from_role, role) OR
             second_predicate(from_role, role)
    END;
' LANGUAGE 'plpgsql';

Would still appreciate an answer that explains why the later works and the former (even with the language changed to plpgsql as suggested by @a_horse_with_no_name) does not, or provides some useful tutorials.

Your function is defined as language sql. There is no return statement in sql, only in PL/pgSQL — user330315
– user330315, Commented Dec 2, 2014 at 21:29
@a_horse_with_no_name I changed language sql to langauge plpgsql, same error. — jjm
– jjm, Commented Dec 2, 2014 at 21:44
Works with language sql: sqlfiddle.com/#!15/7cf4a/1 and plpgsql: sqlfiddle.com/#!15/90f62/1 Btw: the language name is an identifier, so no single quotes please plpgsql or sql but not 'plpgsql' — user330315
– user330315, Commented Dec 2, 2014 at 21:57
@a_horse_with_no_name sorry, I was doing something else stupid. Thanks. — jjm
– jjm, Commented Dec 2, 2014 at 22:30

Erwin Brandstetter · Accepted Answer · 2025-02-05 03:38:43Z

7

SQL

CREATE OR REPLACE FUNCTION is_visible_to(role integer, from_role integer)
  RETURNS boolean
  LANGUAGE sql STABLE STRICT AS
$func$
   SELECT $1 = $2 OR first_predicate($1, $2) OR second_predicate($2, $1);
$func$;

With SELECT. There is no RETURN in plain SQL. Details in the manual here.

Or with short "standard SQL" notation - which happens to use a RETURN keyword (added with Postgres 14, years after your question):

CREATE OR REPLACE FUNCTION is_visible_to(role integer, from_role integer)
  RETURNS boolean
  LANGUAGE sql STABLE STRICT
RETURN $1 = $2 OR first_predicate($1, $2) OR second_predicate($2, $1);

See:

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

PL/pgSQL

CREATE OR REPLACE FUNCTION is_visible_to(role integer, from_role integer)
  RETURNS boolean
  LANGUAGE plpgsql STABLE STRICT AS
$func$
BEGIN
   RETURN $1 = $2 OR first_predicate($1, $2) OR second_predicate($2, $1);
END
$func$;

BEGIN and END are required for PL/pgSQL. Details in the manual here.

Don't use the outdated (and redundant here) ALIAS FOR syntax. It's discouraged in the manual. You can reference parameter names directly. In SQL functions, too, since Postgres 9.2.

edited Feb 5 at 3:38

answered Dec 3, 2014 at 6:57

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jay Over a year ago

Is there a reason you refer to the arguments as $1 and $2, instead of role and from_role in the function body?

Erwin Brandstetter Over a year ago

@Jay: Either is valid in Postgres 9.2+ functions. The OP did not disclose his version, so positional references were the safe bet. There is a hint at the end of my answer. More: stackoverflow.com/a/27116711/939860

Collectives™ on Stack Overflow

Returning boolean from SQL function

1 Answer 1

SQL

PL/pgSQL

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

SQL

PL/pgSQL

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related