Execute query passed in argument in Postgresql

Question

I'm learning plpgsql and I have an exercise where I have to check if a query passed in function argument raises an exception or not and for that I need to run that query but I have no idea how to that.

For the moment I have this :

CREATE OR REPLACE FUNCTION exec(query varchar(100)) RETURNS BOOLEAN AS $$
BEGIN
    BEGIN
        raise notice 'query : %', query;
        PERFORM query;
        return 1;
    EXCEPTION
when undefined_table then 
raise notice 'undefined table';

WHEN undefined_column THEN
 raise notice 'undefined column';

WHEN foreign_key_violation THEN
 raise notice 'foreign key violation ';

WHEN syntax_error THEN
 raise notice 'syntaxe error';
END;
return 0;
END
$$ LANGUAGE PLPGSQL;

Right now the problem I have is with the 5th line. It's where I'd want to perform the query to catch an error and then return 1 if no exception is caught or else return 0 after the "END" if there's an error.

Thank you for your help!

read manual 43.5.2. PERFORM query; will always true. query is a string literal. so it's like select a string literal. it will not have any error. even select '1/0' will be true. — jian
– jian, Commented Nov 6, 2022 at 11:35

Pavel Stehule · Accepted Answer · 2022-11-06 05:21:58Z

1

You cannot to execute dynamic query by statement PERFORM. You should to use statement EXECUTE.

CREATE OR REPLACE FUNCTION exec(query text)
RETURNS BOOLEAN
AS $$
BEGIN
  RAISE NOTICE 'query : %', query;
  EXECUTE query;
  RETURN true;

EXCEPTION
  WHEN when undefined_table THEN 
    RAISE NOTICE 'undefined table';
    
  WHEN undefined_column THEN
    RAISE NOTICE 'undefined column';

  WHEN foreign_key_violation THEN
    RAISE NOTICE 'foreign key violation ';

  WHEN syntax_error THEN
    RAISE NOTICE 'syntax error';

  RETURN false;
END;
$$ LANGUAGE PLPGSQL;

Good start for programming in PL/pgSQL is reading related documentation. It has only few pages. The programming of stored procedures in PL/pgSQL is partially different from application's programming or from programming of stored procedures in other RDBMS.

answered Nov 6, 2022 at 5:21

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

1 Comment

hey Over a year ago

Thank you very much, I'll go and read that straight away! 👍

Collectives™ on Stack Overflow

Execute query passed in argument in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related