Re: pgsql function doesn't work

On Wed, 18 Jun 2003, Erwin Ambrosch wrote:

> Hi,
>
> the function bellow is created successfully, but executing it forces the
> following error message:
>
> WARNING:  Error occurred while executing PL/pgSQL function
> drop_table_if_exists
> WARNING:  line 5 at select into variables
> ERROR:  parser: parse error at or near "$1" at character 34
>
>
> CREATE OR REPLACE FUNCTION drop_table_if_exists(TEXT) RETURNS BOOLEAN AS '
> DECLARE
>    rec RECORD;
> BEGIN
>
>    SELECT INTO rec off_mitarbeiter_id FROM $1;

You can't do this in plpgsql without some form of EXECUTE.

>    IF FOUND THEN
>      EXECUTE ''DROP TABLE'' || '' '' || $1;
>      RETURN true;
>    END IF;
>
>    RETURN false;
>
> END;'
> LANGUAGE 'plpgsql';

However, AFAICS this will error if the table doesn't exist (selecting from
a non-existant table) rather than do nothing which I'm guessing was the
point since otherwise you could do the drop without the function. You
might want to consider querying the system catalogs instead.