how do I return a bit in postgres

Question

I am new to postgres and trying to setup a function that returns a bit.

I keep getting the error

Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.

I understand that

Unless the function is declared to return void, the last statement must be a SELECT, or an INSERT, UPDATE, or DELETE that has a RETURNING clause.

here is the code

CREATE OR REPLACE FUNCTION "f"(...)
  RETURNS bit AS
 DO $$
 Begin
        IF  someStuff
        THEN 
           0; //also tried select 0 //also tried return 0
        ELSE
           1;  //also tried select 1 //also tried return 0
        END IF;
        0; //also tried select 0 //also tried return 0
END $$

Where am I going wrong with the syntax?

user330315 · Accepted Answer · 2013-03-27 15:34:57Z

2

There are several errors:

the DO is wrong in a function definition
you are missing the specification of the language
in PL/pgSQL you use return to return the function's result

So your function becomes:

CREATE OR REPLACE FUNCTION f(some_value integer)
  RETURNS bit AS
 $$
 Begin
    IF (some_value = 1)
    THEN 
       return 0; 
    ELSE
       return 1; 
    END IF;
END $$
language plpgsql

But you should use boolean instead of bit to return true/false flags:

CREATE OR REPLACE FUNCTION f(some_value integer)
  RETURNS boolean AS
 $$
 Begin
    IF (some_value = 1)
    THEN 
       return false; 
    ELSE
       return true; 
    END IF;
END $$
language plpgsql

answered Mar 27, 2013 at 15:34

user330315

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

Comments

Community · Accepted Answer · 2017-05-23 10:28:28Z

1

If you want to use plpgsql then do as in the a_horse's answer but if you don't need plpgsql do in sql:

create or replace function f(some_value integer)
returns boolean as $$

    select some_value = 1;

$$
language sql;

If the function is the one from this question then this will do it:

create or replace function isPersonQualifiedForJob(pid integer, jid)
returns boolean as $$

    select exists (
        select 1
        from
            getskillsforjob(jid) j
            inner join
            getskillsforperson(pid) p on j.skillid = p.skillid
    )

$$
language sql;

Checking for exists is much faster then counting since it is enough to find the first match.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Mar 27, 2013 at 15:40

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

user330315 Over a year ago

Even better, but I think the obfuscated someStuff "requirement" will turn out to be more complicated.

Clodoaldo Neto Over a year ago

@a_horse I think I found the someStuff and if so the sql version is still adequate. See update.

Collectives™ on Stack Overflow

how do I return a bit in postgres

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related