Thread: pgsql function doesn't work

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;

   IF FOUND THEN
     EXECUTE ''DROP TABLE'' || '' '' || $1;
     RETURN true;
   END IF;

   RETURN false;

END;'
LANGUAGE 'plpgsql';
select drop_table_if_exists('off_jahres_abr_2003');

If I hardcode the table name istead of using $1, everything works fine.


Please help.

Thanx Erwin

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.

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@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Stephan Szabo
Sent: Thursday, June 19, 2003 1:24 AM
To: Erwin Ambrosch
Cc: pgsql-admin@postgresql.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