Thread: SQL Script

I have written the following function but when I try to run it, I get Error:
ERROR:  parser: parse error at or near "". As you can see, there is no
instance of "" in my function. Does anyone have any idea why this is
happening?

CREATE FUNCTION dropifexists(text)
RETURNS text
AS 'DECLARE
   numcount integer;
   nameoftable text;
BEGIN
   nameoftable := lower($1);
   SELECT INTO numcount count(tablename)
   FROM pg_tables
   WHERE tablename = nameoftable;

   if numcount = 1
   then DROP TABLE nameoftable;
   end if;
   RETURN nameoftable;
END;'

On Fri, Mar 30, 2001 at 06:50:15PM +0000, Kevin Willems wrote:
> I have written the following function but when I try to run it, I get Error:
> ERROR:  parser: parse error at or near "". As you can see, there is no
> instance of "" in my function. Does anyone have any idea why this is
> happening?
>
> CREATE FUNCTION dropifexists(text)
> RETURNS text
> AS 'DECLARE
>    numcount integer;
>    nameoftable text;
> BEGIN
>    nameoftable := lower($1);
>    SELECT INTO numcount count(tablename)
>    FROM pg_tables
>    WHERE tablename = nameoftable;
>
>    if numcount = 1
>    then DROP TABLE nameoftable;
>    end if;
>    RETURN nameoftable;
> END;'

You forgot to add " LANGUAGE 'plpgsql'; " at the end (no dbl quotes).

--
Eric G. Miller <egm2@jps.net>

Kevin Willems writes:

> I have written the following function but when I try to run it, I get Error:
> ERROR:  parser: parse error at or near "". As you can see, there is no
> instance of "" in my function. Does anyone have any idea why this is
> happening?

"" means 'empty string', which often indicates end of input.

>
> CREATE FUNCTION dropifexists(text)
> RETURNS text
> AS 'DECLARE
>    numcount integer;
>    nameoftable text;
> BEGIN
>    nameoftable := lower($1);
>    SELECT INTO numcount count(tablename)
>    FROM pg_tables
>    WHERE tablename = nameoftable;
>
>    if numcount = 1
>    then DROP TABLE nameoftable;
>    end if;
>    RETURN nameoftable;
> END;'

You're missing LANGUAGE '...' here.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/