Thread: error while generating and EXECUTEing a query dynamically
Hi all,
The machine I am sending this mail from does not have postgres, but I
did this just this morning so am typing from memory:
<code>
CREATE FUNCTION footable() RETURNS INTEGER AS '
DECLARE
sql VARCHAR(1000);
BEGIN
sql := ''CREATE TABLE foo (x INT, y CHAR) '';
EXECUTE sql;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
<code>
Now when I try to execute this function I get an error message saying
<error>
parse error at or near ""
<error>
I will get the exact function and error output from other machine
tomorrow (the actual query I am generating is more complex) but in the
meantime if anyone can help with this example, I would be most
grateful. Where am I going wrong?
TIA
Gaurav
On Wed, Sep 12, 2001 at 11:45:16PM +0530, Gaurav Priyolkar wrote: > Hi all, > > The machine I am sending this mail from does not have postgres, but I > did this just this morning so am typing from memory: > > <code> > > CREATE FUNCTION footable() RETURNS INTEGER AS ' > > DECLARE > sql VARCHAR(1000); > > BEGIN > > sql := ''CREATE TABLE foo (x INT, y CHAR) ''; > > EXECUTE sql; > > RETURN 1; > > END; > ' LANGUAGE 'plpgsql'; > > > <code> > > > Now when I try to execute this function I get an error message saying > > <error> > parse error at or near "" > <error> > > > I will get the exact function and error output from other machine > tomorrow (the actual query I am generating is more complex) but in the > meantime if anyone can help with this example, I would be most > grateful. Where am I going wrong? > > TIA > > Gaurav > > > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Sleep: A completely inadequate substitute for caffeine.
Your function worked fine for me (I copied and pasted). What PostgeSQL version are you running? EXECUTE is a new feature for version 7.1. ----- Original Message ----- From: "Gaurav Priyolkar" <gaurav_lists@yahoo.com> To: <pgsql-general@postgresql.org> Sent: Wednesday, September 12, 2001 1:15 PM Subject: [GENERAL] error while generating and EXECUTEing a query dynamically > Hi all, > > The machine I am sending this mail from does not have postgres, but I > did this just this morning so am typing from memory: > > <code> > > CREATE FUNCTION footable() RETURNS INTEGER AS ' > > DECLARE > sql VARCHAR(1000); > > BEGIN > > sql := ''CREATE TABLE foo (x INT, y CHAR) ''; > > EXECUTE sql; > > RETURN 1; > > END; > ' LANGUAGE 'plpgsql'; > > > <code> > > > Now when I try to execute this function I get an error message saying > > <error> > parse error at or near "" > <error> > > > I will get the exact function and error output from other machine > tomorrow (the actual query I am generating is more complex) but in the > meantime if anyone can help with this example, I would be most > grateful. Where am I going wrong? > > TIA > > Gaurav > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
Hi all, Sorry for following up to my own mail. I went over the function again and realized I was exceeding the size I had allocated for the query string. Never realised I was exceeding the varchar(1000) that I had declared as the string. Apologies for my last follow-up to my query, I sent it from the wrong id and it seems as if the text of my message got stripped out and only quoted part got through.(In fact I remember having snipped most of my original mail while sending too :( -Gaurav On Wed, Sep 12, 2001 at 11:45:16PM +0530, Gaurav Priyolkar wrote: > Hi all, > > The machine I am sending this mail from does not have postgres, but I > did this just this morning so am typing from memory: > <snip> -- Sleep: A completely inadequate substitute for caffeine.