Re: pgsql function doesn't work

taking queue from Stephan, this would achieve what you want to do though it
i don't find it too 'neat':

DROP FUNCTION drop_table_if_exists(TEXT);
CREATE FUNCTION drop_table_if_exists(TEXT) RETURNS BOOLEAN AS '
DECLARE
str_tmp text;
BEGIN
SELECT INTO str_tmp tablename FROM pg_tables WHERE tablename = $1;
IF FOUND THEN
EXECUTE ''DROP TABLE'' || '' '' || $1;
RETURN true;
END IF;
RETURN false;
END;'
LANGUAGE 'plpgsql';

HTH.

--
Mel Jamero

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Stephan Szabo
Sent: Thursday, June 19, 2003 1:24 AM
To: Erwin Ambrosch
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] 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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend