Create a function using execute (plpgsql)

Question

I want to make a function that returns the next value of a primary key based on the table name:

CREATE OR REPLACE FUNCTION next_pk (_table varchar) RETURNS INTEGER AS $$
    BEGIN
        RETURN EXECUTE 'SELECT COALESCE (MAX (primary_key), 0) + 1 FROM ' || quote_ident (_table) || ' limit 1';
    END;
$$ LANGUAGE plpgsql;

select next_pk ('myTable');

But it gives the following error:

ERROR:  type "execute" does not exist
LINE 1: SELECT EXECUTE 'SELECT COALESCE (MAX (primary_key), 0) + 1 F...

Any clues ? Thanks.

Sequence does not support rollback

Caio
– Caio

2013-02-01 10:02:26 +00:00
Commented Feb 1, 2013 at 10:02 — Caio
– Caio, Commented Feb 1, 2013 at 10:02

klin · Accepted Answer · 2013-01-31 23:28:02Z

1

You need integer variable and 'execute into':

CREATE OR REPLACE FUNCTION next_pk (_table varchar) RETURNS INTEGER AS $$
DECLARE
    result integer;    
BEGIN  
    EXECUTE 
        'SELECT COALESCE (MAX (primary_key), 0) + 1 FROM ' || quote_ident (_table) 
        INTO result;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

select next_pk ('myTable');

answered Jan 31, 2013 at 23:28

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Create a function using execute (plpgsql)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related