SQL Script

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/