Execute query passed in argument in Postgresql-CodePudding

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!

CodePudding user response：

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.