How to assign a boolean value returned from a function to a variable in PostgreSQL 9.4?

Question

I have a function that checks whether a table exists on PostgreSQL or not, using the following code:

CREATE OR REPLACE FUNCTION public.sp_table_exists(p_in_table_name character varying)
  RETURNS boolean AS
$$
    DECLARE QUERY_COUNT INTEGER DEFAULT 1;
    QUERY_STRING VARCHAR(300);
    BEGIN
        QUERY_STRING := CONCAT('SELECT RELNAME FROM PG_CLASS WHERE RELNAME = ''',p_in_table_name,'''');
        EXECUTE QUERY_STRING;
        GET DIAGNOSTICS QUERY_COUNT = ROW_COUNT;
        IF QUERY_COUNT > 0  THEN
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;     
    END;
$$ LANGUAGE plpgsql;

I'm trying to use the output of the above function to assign to a boolean value, but PostgreSQL doesn't allow me to do so.

DECLARE DEBUG_ENABLED boolean DEFAULT FALSE;
DEBUG_ENABLED := PERFORM sp_table_exists('temp_table');

OR

DEBUG_ENABLED := SELECT * FROM sp_table_exists('temp_table');

Can you please help me resolve this?

Hambone · Accepted Answer · 2016-02-15 12:21:14Z

2

Perform, to the best of my understanding, lets you execute a function without returning any value(s). As such, it makes sense that this would return nothing.

As far as assigning it to your variable, I think it's easier than you imagined:

DEBUG_ENABLED := sp_table_exists('temp_table');

select ... into is generally used when you have a field or value from a query that you want in a variable (which isn't your situation):

select count (*) > 0
into DEBUG_ENABLED
from information_schema.tables
where table_name = 'temp_table'

answered Feb 15, 2016 at 12:21

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:29:33Z

1

Assuming pl/pgsql, this should work:

SELECT sp_table_exists('temp_table')
  INTO DEBUG_ENABLED

Looks like you're being bugged by something outside Postgres. See this query.

The CREATE FUNCTION / SELECT INTO works fine at my end.

testdb=# CREATE OR REPLACE FUNCTION public.sp_table_exists(p_in_table_name character varying)
testdb-#   RETURNS boolean AS
testdb-# $$
testdb$# DECLARE QUERY_COUNT INTEGER DEFAULT 1;
testdb$# QUERY_STRING VARCHAR(300);
testdb$#     BEGIN
testdb$#         QUERY_STRING := CONCAT('SELECT RELNAME FROM PG_CLASS WHERE RELNAME = ''',p_in_table_name,'''');
testdb$#         EXECUTE QUERY_STRING;
testdb$#         GET DIAGNOSTICS QUERY_COUNT = ROW_COUNT;
testdb$#         IF QUERY_COUNT > 0  THEN
testdb$#             RETURN TRUE;
testdb$#         ELSE
testdb$#             RETURN FALSE;
testdb$#         END IF;
testdb$#     END;
testdb$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
testdb=# SELECT sp_table_exists('temp_table')
testdb-#   INTO DEBUG_ENABLED;
SELECT 1
testdb=#

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Feb 15, 2016 at 11:18

Robins Tharakan

2,52122 silver badges19 bronze badges

5 Comments

N00b Pr0grammer Over a year ago

I'm using pl/pgsql, your suggestion still gives me the same error as earlier. "Invalid type name" :(

Robins Tharakan Over a year ago

BTW... just checked. The CREATE FUNCTION and SELECT INTO works perfectly on my local postgres installation.

N00b Pr0grammer Over a year ago

This is frustrating, I've tried all the combinations from the above and also your suggestion - still the same error!

Robins Tharakan Over a year ago

Just to cross-check. You are running this directly on psql prompt right (if so, could you tell the postgres version)? If not, could you elaborate how are you running this? Is it via an external script (python / ruby etc.)?

N00b Pr0grammer Over a year ago

I'm running this on pgAdmin - running this on two PostgreSQL versions (9.4.5 & 9.5)

Collectives™ on Stack Overflow

How to assign a boolean value returned from a function to a variable in PostgreSQL 9.4?

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related