Thread: error while generating and EXECUTEing a query dynamically

error while generating and EXECUTEing a query dynamically

From
Gaurav Priyolkar
Date:
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


Re: error while generating and EXECUTEing a query dynamically

From
Gaurav Priyolkar
Date:
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.

Re: error while generating and EXECUTEing a query dynamically

From
"Jeff Eckermann"
Date:
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
>
>


Re: error while generating and EXECUTEing a query dynamically

From
Gaurav Priyolkar
Date:
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.