FOUND variable set to `true` when there are NO records

Question

In a PLPGSQL function I have some lines to check if a record exists. Well, the FOUND variable is set to TRUE even if there are no records. Very weird thing.

The lines where I am testing are:

query:='SELECT i.identity_id FROM app.identity as i,' || quote_ident(schema_name) || '.users as u,' || quote_ident(schema_name) || '.udata as ud WHERE i.identity_id=' || p_identity_id || ' and u.identity_id=i.identity_id and ud.identity_id=i.identity_id';
        RAISE WARNING 'query=%',query;
        PERFORM query;
        RAISE WARNING 'FOUND=%',FOUND;
        IF FOUND 
        THEN
            RAISE WARNING 'Record found, rising an exception';
            RAISE EXCEPTION USING ERRCODE = 'AA002';
        END IF;

This is the output:

WARNING:  query=SELECT i.identity_id FROM app.identity as i,"comp-1049007476".users as u,"comp-1049007476".udata as ud WHERE i.identity_id=-1615382132 and u.identity_id=i.identity_id and ud.identity_id=i.identity_id
WARNING:  FOUND=t
WARNING:  Record found, rising an exception

And this is pg_log :

 2017-01-09 09:47:21.906 CST > LOG:  execute <unnamed>: SELECT app.create_identity($1::varchar,'') as identity_id
< 2017-01-09 09:47:21.906 CST > DETAIL:  parameters: $1 = 'someuser@domain.com'
< 2017-01-09 09:47:21.908 CST > WARNING:  new_identity_id=-1615382132
< 2017-01-09 09:47:21.908 CST > CONTEXT:  PL/pgSQL function app.create_identity(character varying,character varying) line 18 at RAISE
< 2017-01-09 09:47:21.917 CST > LOG:  execute <unnamed>: select app.add_user(       
                        $1::integer,
                        $2::varchar,                     
                        $3::integer,                     
                        $4::varchar,                     
                        $5::varchar,                     
                        $6::varchar,                     
                        $7::varchar,                     
                        $8::varchar,                     
                        $9::varchar,                     
                        $10::varchar,                    
                        $11::integer,                    
                        $12::integer,                    
                        $13::integer,                    
                        $14::integer,                    
                        $15::integer,                    
                        $16::integer,                    
                        $17::smallint,                   
                        $18::boolean,                    
                        $19::boolean,                    
                        $20::boolean,                    
                        $21::boolean,                    
                        $22::boolean                     
                        )
< 2017-01-09 09:47:21.917 CST > DETAIL:  parameters: $1 = '905220468', $2 = '763715373817831', $3 = '-1049007476', $4 = 'Some User', $5 = '44444', $6 = '', $7 = '', $8 = '', $9 = '', $10 = '', $11 = '-1615382132', $12 = '0', $13 = '1', $14 = '0', $15 = '0', $16 = '0', $17 = '0', $18 = 't', $19 = 'f', $20 = 'f', $21 = 'f', $22 = 'f'
< 2017-01-09 09:47:21.919 CST > WARNING:  query=SELECT i.identity_id FROM app.identity as i,"comp-1049007476".users as u,"comp-1049007476".udata as ud WHERE i.identity_id=-1615382132 and u.identity_id=i.identity_id and ud.identity_id=i.identity_id
< 2017-01-09 09:47:21.919 CST > CONTEXT:  PL/pgSQL function app.add_user(integer,character varying,integer,character varying,character varying,character varying,character varying,character varying,character varying,character varying,integer,integer,integer,integer,integer,integer,smallint,boolean,boolean,boolean,boolean,boolean) line 25 at RAISE
< 2017-01-09 09:47:21.919 CST > WARNING:  FOUND=t
< 2017-01-09 09:47:21.919 CST > CONTEXT:  PL/pgSQL function app.add_user(integer,character varying,integer,character varying,character varying,character varying,character varying,character varying,character varying,character varying,integer,integer,integer,integer,integer,integer,smallint,boolean,boolean,boolean,boolean,boolean) line 27 at RAISE
< 2017-01-09 09:47:21.919 CST > WARNING:  Record found, rising an exception
< 2017-01-09 09:47:21.919 CST > CONTEXT:  PL/pgSQL function app.add_user(integer,character varying,integer,character varying,character varying,character varying,character varying,character varying,character varying,character varying,integer,integer,integer,integer,integer,integer,smallint,boolean,boolean,boolean,boolean,boolean) line 30 at RAISE
< 2017-01-09 09:47:21.919 CST > ERROR:  Identity already registered

Why I am sure that the record does NOT exist? Well, I am putting the values that I know are not in the table, but just in case , I verify:

dev=> SELECT i.identity_id FROM app.identity as i,"comp-1049007476".users as u,"comp-1049007476".udata as ud WHERE i.identity_id=-1615382132 and u.identity_id=i.identity_id and ud.identity_id=i.identity_id;
 identity_id 
-------------
(0 rows)

dev=>

See, 0 records. So, the BIG question is, why does FOUND variable lie? What is happening here?

This is the full code of my function:

CREATE OR REPLACE FUNCTION app.add_user(sess_identity_id int,session_str varchar,sess_company_id int,
    p_full_name         varchar,
    p_mob_phone         varchar,
    p_home_phone        varchar,
    p_work_phone        varchar,
    p_phone_ext         varchar,
    p_position          varchar,
    p_notes             varchar,
    p_identity_id       integer,
    p_user_id           integer,
    p_group_id          integer,
    p_shift_id          integer,
    p_boss_id           integer,
    p_crreated_by       integer,
    p_timezone          smallint,
    p_utype             boolean,
    p_create_root       boolean,
    p_has_subord        boolean,
    p_inactive          boolean,
    p_generic           boolean 
)
RETURNS integer as $$
DECLARE
    v_session                   bigint;
    schema_name                 varchar;
    query                       varchar;
    current_ts                  integer;
    v_user_id                   integer;
    v_generic                   integer;
    v_udata_id                  integer;
    _c                          text;
BEGIN
        SELECT extract(epoch from now())::integer into current_ts;
        SELECT session_str::bigint INTO v_session;
        schema_name:='comp' || sess_company_id;

        IF NOT EXISTS (
            SELECT 1 FROM app.session WHERE app.session.identity_id=sess_identity_id AND app.session.session=v_session
        ) THEN
            RAISE EXCEPTION USING ERRCODE = 'AA001';
        END IF;

        query:='SELECT i.identity_id FROM app.identity as i,' || quote_ident(schema_name) || '.users as u,' || quote_ident(schema_name) || '.udata as ud WHERE i.identity_id=' || p_identity_id || ' and u.identity_id=i.identity_id and ud.identity_id=i.identity_id';
        RAISE WARNING 'query=%',query;
        PERFORM query;
        RAISE WARNING 'FOUND=%',FOUND;
        IF FOUND 
        THEN
            RAISE WARNING 'Record found, rising an exception';
            RAISE EXCEPTION USING ERRCODE = 'AA002';
        END IF;

        query:='SELECT u.user_id,u.generic FROM ' || quote_ident(schema_name) || '.users as u,identity as i WHERE i.email=p_email AND i.identity_id=u.identity_id';
        RAISE WARNING 'query=%',query;
        EXECUTE query INTO v_user_id,v_generic;
        IF NOT FOUND
        THEN
            EXECUTE 'INSERT INTO $1.users(
                identity_id,
                group_id,
                shift_id,
                boss_id,
                created_by,
                date_inserted
                timezone,
                utype,
                create_root,
                has_subord,
                inactive,
                generic
            ) VALUES ($2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) RETURNING user_id' 
                INTO v_user_id 
                USING schema_name,p _identity_id,p_group_id,p_shift_id,p_boss_id,p_created_by,p_date_inserted,p_timezone,p_utype,p_create_root,p_has_subord,p_inactive,p_generc;
        END IF;
        if NOT v_generic THEN
            RAISE EXCEPTION USING ERRCODE = 'AA003';
        END IF;
        EXECUTE 'INSERT INTO $1.udata(user_id,identity_id,date_insert,full_name,mob_phone,home_phone,work_phone,phone_ext,position,notes) VALUES ($2,$3,$4,$5,$6,$7,$8,$9,$10,$11)'
                INTO v_udata_id
                USING schema_name,v_user_id,p_identity_id,current_ts,p_full_name,p_mob_phone,p_home_phone,p_work_phone,p_phone_ext,p_position,p_notes;
        RETURN v_udata_id;
EXCEPTION
    WHEN SQLSTATE 'AA001' THEN
        RAISE EXCEPTION USING ERRCODE = 'AA001', message = 'Invalid session';
    WHEN SQLSTATE 'AA002' THEN
        RAISE EXCEPTION USING ERRCODE = 'AA002', message = 'Identity already registered';
    WHEN SQLSTATE 'AA003' THEN
        RAISE EXCEPTION USING ERRCODE = 'AA003', message = 'Not a generic user, can not add email address';
    WHEN OTHERS THEN
        RAISE NOTICE 'add_user() failed with... error: % %',SQLSTATE,SQLERRM;
        GET STACKED DIAGNOSTICS _c = PG_EXCEPTION_CONTEXT;
        RAISE NOTICE 'context: >>%<<', _c;
        RAISE EXCEPTION USING MESSAGE = 'An error which is not handled by function';
END
$$ LANGUAGE plpgsql;

And the table structure, just in case:

dev=> \d+ app.identity;
                                    Table "app.identity"
    Column     |          Type          | Modifiers | Storage  | Stats target | Description 
---------------+------------------------+-----------+----------+--------------+-------------
 identity_id   | integer                | not null  | plain    |              | 
 created_by    | integer                |           | plain    |              | 
 email         | character varying(128) |           | extended |              | 
 date_inserted | integer                |           | plain    |              | 
 password      | character varying(32)  |           | extended |              | 
 validated     | smallint               |           | plain    |              | 
Indexes:
    "identity_pkey" PRIMARY KEY, btree (identity_id)
    "identity_email_key" UNIQUE CONSTRAINT, btree (email)

dev=> \d+ "comp-1049007476".users;
                                                        Table "comp-1049007476.users"
    Column     |   Type   |                                 Modifiers                                 | Storage | Stats target | Description 
---------------+----------+---------------------------------------------------------------------------+---------+--------------+-------------
 user_id       | integer  | not null default nextval('"comp-1049007476".users_user_id_seq'::regclass) | plain   |              | 
 identity_id   | integer  | default 0                                                                 | plain   |              | 
 group_id      | integer  | default 0                                                                 | plain   |              | 
 shift_id      | integer  | default 0                                                                 | plain   |              | 
 boss_id       | integer  | default 0                                                                 | plain   |              | 
 created_by    | integer  | default 0                                                                 | plain   |              | 
 date_inserted | integer  | default 0                                                                 | plain   |              | 
 timezone      | smallint | default 0                                                                 | plain   |              | 
 utype         | boolean  | default false                                                             | plain   |              | 
 create_root   | boolean  | default false                                                             | plain   |              | 
 has_subord    | boolean  | default false                                                             | plain   |              | 
 inactive      | boolean  | default false                                                             | plain   |              | 
 generic       | boolean  | default false                                                             | plain   |              | 

dev-> \d+ "comp-1049007476".udata 
                                                                Table "comp-1049007476.udata"
    Column     |          Type          |                                 Modifiers                                  | Storage  | Stats target | Description 
---------------+------------------------+----------------------------------------------------------------------------+----------+--------------+-------------
 udata_id      | integer                | not null default nextval('"comp-1049007476".udata_udata_id_seq'::regclass) | plain    |              | 
 user_id       | integer                | default 0                                                                  | plain    |              | 
 identity_id   | integer                | default 0                                                                  | plain    |              | 
 date_inserted | integer                | default 0                                                                  | plain    |              | 
 full_name     | character varying(64)  | default ''::character varying                                              | extended |              | 
 mob_phone     | character varying(16)  | default ''::character varying                                              | extended |              | 
 home_phone    | character varying(16)  | default ''::character varying                                              | extended |              | 
 work_phone    | character varying(16)  | default ''::character varying                                              | extended |              | 
 phone_ext     | character varying(8)   | default ''::character varying                                              | extended |              | 
 position      | character varying(64)  | default ''::character varying                                              | extended |              | 
 notes         | character varying(128) | default ''::character varying                                              | extended |              | 

dev->

maybe try to add `GET DIAGNOSTICS someyourvariable = ROW_COUNT;` and display variable and you will see if there are some records or not — JosMac, Jan 09 '17 at 16:07
line 3: `PERFORM` should be `EXECUTE` but see Pavel's response. — Jasen, Jan 09 '17 at 20:06
About `EXECUTE`, `FOUND` and `GET DIAGNOSTICS` in plpgsql: http://stackoverflow.com/questions/8449011/dynamic-sql-execute-as-condition-for-if-statement/8449341#8449341 — Erwin Brandstetter, Jan 11 '17 at 05:16

Pavel Stehule · Accepted Answer · 2017-01-09T17:15:21.870

7

The basic problem is in usage of PERFORM statement. This statement is designed for function evaluation without result processing. It is not designed for dynamic SQL (where SQL is entered as string).

PLpgSQL translate almost all statements to SELECTs. PERFORM is not a exception.

PERFORM fx(10,20);

is translated to:

SELECT fx(10,20);

Your code:

variable := 'SELECT * FROM foo';
PERFORM variable;

is translated to:

SELECT 'SELECT * FROM foo'; -- it is same like SELECT 'hello';

This query returns one row and the variable FOUND should be true. So nothing is weird.

In PLpgSQL you should to strongly differ between static SQL and dynamic SQL. Dynamic SQL (the query is assembled in runtime) is supported only with EXECUTE statement.

The usage PERFORM variable is simple wrong usage of this statement - but it generates valid SQL statement, that can be evaluated without runtime error.

edited Jan 09 '17 at 17:15

answered Jan 09 '17 at 17:09

Pavel Stehule

42,331
5
91
94

Thanks!!! It is a perfect explanation of what is happening and I got it. However, I can't use `EXECUTE` because it does not affect the `FOUND` variable. That's why I was trying to use `PEFORM`... – Nulik Jan 10 '17 at 00:15
By any chance, do you know how can I construct the query for `PEROFRM` command by concatenating my schema names `|| quote_ident(schema_name) ||` ?? When I try to use some string concatenation in PERFORM it produces lots of errors. – Nulik Jan 10 '17 at 00:16
my schema names contain special characters and that's why I have to use quote_ident() function but I can't insert it in `PERFORM` statement. – Nulik Jan 10 '17 at 00:30
@Nulik if you use `EXECUTE`, then you have to check ROW_COUNT via statement `GET DIAGNOSTICS`. When schema uses some special characters, then use double quotes for this - just `INSERT INTO "some obscure schema".foo VALUES(10)` – Pavel Stehule Jan 10 '17 at 05:10
Thanks again. But `GET DIAGNOSTICS` seems to be some obscure debugging command, is it `OK` to use such statements? – Nulik Jan 10 '17 at 05:43
1

`GET DIAGNOSTICS` is ANSI/SQL standard normal statement - there is not any problem with this statement. It is not designed for debugging. It is designed for daily work. – Pavel Stehule Jan 10 '17 at 11:27

FOUND variable set to `true` when there are NO records

1 Answers1