Thread: help with dynamic table name

help with dynamic table name

From

"Peter Schonefeld"

Date:

05 March 2007, 23:59:53

Hi, could someone please let me know what i'm doing wrong here?

CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text,varchar(32)) RETURNS text AS $$

DECLARE sql text;
BEGIN
sql := "INSERT INTO "+ $3 +" ('id','body') VALUES ("+ $1 +","+ $2 +")";
EXECUTE sql;
RETURN '';
END;

$$ LANGUAGE 'plpgsql' VOLATILE;

I get the error: "ERROR: 42703: column \"INSERT INTO \" does not exist"

Cheers
Pete

Re: help with dynamic table name

From

"Jasbinder Singh Bali"

Date:

06 March 2007, 00:27:21

try replacing double quotes with single quotes and you should be fine i think

~Jas

On 3/5/07, Peter Schonefeld <peter.schonefeld@gmail.com> wrote:

Hi, could someone please let me know what i'm doing wrong here?

CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text,varchar(32)) RETURNS text AS $$

DECLARE sql text;
BEGIN
sql := "INSERT INTO "+ $3 +" ('id','body') VALUES ("+ $1 +","+ $2 +")";
EXECUTE sql;
RETURN '';
END;

$$ LANGUAGE 'plpgsql' VOLATILE;

I get the error: "ERROR: 42703: column \"INSERT INTO \" does not exist"

Cheers
Pete

Re: help with dynamic table name

From

"Peter Schonefeld"

Date:

06 March 2007, 00:36:44

Hi Jas, thanks for the quick reply...I had started out with the single quotes, but had chaged it in my many efforts to get the thing to work...with the single quotes i'm getting the error:

"ERROR: 42883: operator does not exist: \"unknown\" + character varying"

====

CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text

,varchar(32)) RETURNS text AS $$

DECLARE sql text;
BEGIN
sql := 'INSERT INTO '+ $3 +' ("id","body") VALUES ('+ $1 +','+ $2 +')';
EXECUTE sql;
RETURN '';
END;

$$ LANGUAGE 'plpgsql' VOLATILE;

Pete

On 3/6/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:

try replacing double quotes with single quotes and you should be fine i think
~Jas

On 3/5/07, Peter Schonefeld <peter.schonefeld@gmail.com > wrote:
Hi, could someone please let me know what i'm doing wrong here?

CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text,varchar(32)) RETURNS text AS $$

DECLARE sql text;
BEGIN
sql := "INSERT INTO "+ $3 +" ('id','body') VALUES ("+ $1 +","+ $2 +")";
EXECUTE sql;
RETURN '';
END;

$$ LANGUAGE 'plpgsql' VOLATILE;

I get the error: "ERROR: 42703: column \"INSERT INTO \" does not exist"

Cheers
Pete

Re: help with dynamic table name

From

"Jasbinder Singh Bali"

Date:

06 March 2007, 00:42:23

sql := 'INSERT INTO '+ $3 +' (id,body) VALUES ('+ $1 +','+ $2 +')';
remove the double quotes with id and body and see if it works.

~Jas

On 3/5/07, Peter Schonefeld <peter.schonefeld@gmail.com> wrote:

Hi Jas, thanks for the quick reply...I had started out with the single quotes, but had chaged it in my many efforts to get the thing to work...with the single quotes i'm getting the error:

"ERROR: 42883: operator does not exist: \"unknown\" + character varying"

====

CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text
,varchar(32)) RETURNS text AS $$

DECLARE sql text;
BEGIN
sql := 'INSERT INTO '+ $3 +' ("id","body") VALUES ('+ $1 +','+ $2 +')';
EXECUTE sql;
RETURN '';
END;

$$ LANGUAGE 'plpgsql' VOLATILE;

Pete

On 3/6/07, Jasbinder Singh Bali <jsbali@gmail.com > wrote:
try replacing double quotes with single quotes and you should be fine i think
~Jas

On 3/5/07, Peter Schonefeld <peter.schonefeld@gmail.com > wrote:
Hi, could someone please let me know what i'm doing wrong here?

CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text,varchar(32)) RETURNS text AS $$

DECLARE sql text;
BEGIN
sql := "INSERT INTO "+ $3 +" ('id','body') VALUES ("+ $1 +","+ $2 +")";
EXECUTE sql;
RETURN '';
END;

$$ LANGUAGE 'plpgsql' VOLATILE;

I get the error: "ERROR: 42703: column \"INSERT INTO \" does not exist"

Cheers
Pete

Re: help with dynamic table name

From

"Jasbinder Singh Bali"

Date:

06 March 2007, 00:48:53

sql := 'INSERT INTO '+ $3 +' (id,body) VALUES ('+ $1 +','+ $2 +')';
remove the double quotes with id and body and see if it works.

~Jas

On 3/5/07, Peter Schonefeld <peter.schonefeld@gmail.com> wrote:

Hi Jas, thanks for the quick reply...I had started out with the single quotes, but had chaged it in my many efforts to get the thing to work...with the single quotes i'm getting the error:

"ERROR: 42883: operator does not exist: \"unknown\" + character varying"

====

CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text
,varchar(32)) RETURNS text AS $$

DECLARE sql text;
BEGIN
sql := 'INSERT INTO '+ $3 +' ("id","body") VALUES ('+ $1 +','+ $2 +')';
EXECUTE sql;
RETURN '';
END;

$$ LANGUAGE 'plpgsql' VOLATILE;

Pete

On 3/6/07, Jasbinder Singh Bali <jsbali@gmail.com > wrote:
try replacing double quotes with single quotes and you should be fine i think
~Jas

On 3/5/07, Peter Schonefeld <peter.schonefeld@gmail.com > wrote:
Hi, could someone please let me know what i'm doing wrong here?

CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text,varchar(32)) RETURNS text AS $$

DECLARE sql text;
BEGIN
sql := "INSERT INTO "+ $3 +" ('id','body') VALUES ("+ $1 +","+ $2 +")";
EXECUTE sql;
RETURN '';
END;

$$ LANGUAGE 'plpgsql' VOLATILE;

I get the error: "ERROR: 42703: column \"INSERT INTO \" does not exist"

Cheers
Pete

Re: help with dynamic table name

From

Tom Lane

Date:

06 March 2007, 00:48:58

"Jasbinder Singh Bali" <jsbali@gmail.com> writes:
> On 3/5/07, Peter Schonefeld <peter.schonefeld@gmail.com> wrote:
>> sql := "INSERT INTO "+ $3 +" ('id','body') VALUES ("+ $1 +","+ $2 +")";
>>
>> I get the error: "ERROR: 42703: column \"INSERT INTO \" does not exist"

> try replacing double quotes with single quotes and you should be fine i
> think

Actually, I think Peter's got the double and single quotes exactly
backwards.  You can't just randomly use one or the other: single quotes
surround literal strings, double quotes surround identifiers (and are
only really needed if the identifier wouldn't be syntactically an
identifier without them).  So Peter could write

  sql := 'INSERT INTO '+ $3 +' ("id","body") VALUES ('+ $1 +','+ $2 +')';

but in this particular case there's no point in double-quoting those
target-column names; it'd be the same to write

  sql := 'INSERT INTO '+ $3 +' (id,body) VALUES ('+ $1 +','+ $2 +')';

            regards, tom lane

Re: help with dynamic table name

From

"Jasbinder Singh Bali"

Date:

06 March 2007, 00:57:12

thats wot i suggested him in my last email :)

~Jas

On 3/5/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Jasbinder Singh Bali" <jsbali@gmail.com> writes:
> On 3/5/07, Peter Schonefeld <peter.schonefeld@gmail.com> wrote:
>> sql := "INSERT INTO "+ $3 +" ('id','body') VALUES ("+ $1 +","+ $2 +")";
>>
>> I get the error: "ERROR: 42703: column \"INSERT INTO \" does not exist"

> try replacing double quotes with single quotes and you should be fine i
> think

Actually, I think Peter's got the double and single quotes exactly
backwards.  You can't just randomly use one or the other: single quotes
surround literal strings, double quotes surround identifiers (and are
only really needed if the identifier wouldn't be syntactically an
identifier without them).  So Peter could write

sql := 'INSERT INTO '+ $3 +' ("id","body") VALUES ('+ $1 +','+ $2 +')';

but in this particular case there's no point in double-quoting those
target-column names; it'd be the same to write

sql := 'INSERT INTO '+ $3 +' (id,body) VALUES ('+ $1 +','+ $2 +')';

                       regards, tom lane

Re: help with dynamic table name

From

Stephan Szabo

Date:

06 March 2007, 01:07:05

On Tue, 6 Mar 2007, Peter Schonefeld wrote:

> Hi Jas, thanks for the quick reply...I had started out with the single
> quotes, but had chaged it in my many efforts to get the thing to work...with
> the single quotes i'm getting the error:
>
> "ERROR: 42883: operator does not exist: \"unknown\" + character varying"
>
> ====
>
> CREATE OR REPLACE FUNCTION sc_insert_row(char(32),text,varchar(32)) RETURNS
> text AS $$
>
> DECLARE sql text;
> BEGIN
>   sql := 'INSERT INTO '+ $3 +' ("id","body") VALUES ('+ $1 +','+ $2 +')';

In addition to the conversion to single quotes, you probably mean to be
using || (string concatenation) rather than + (addition) in the above.

Re: help with dynamic table name

From

"Peter Schonefeld"

Date:

06 March 2007, 01:18:02

Got it!

sql := 'INSERT INTO '|| $3 ||' (id,body) VALUES ('''|| $1 ||''','''|| $2 ||''')';

Thanks all :)
Pete

Re: help with dynamic table name

From

"Peter Schonefeld"

Date:

06 March 2007, 01:19:49

Still no joy...if i forget about the variables and just try buidling a string to execute, eg:

sql := 'INSERT INTO application (id,body) VALUES (''asdf'',''lkjh'')';

It works.

but with the variables i still get an error...

I tried putting quotes (two single quote chars) around the VALUES variables

sql := 'INSERT INTO '+ $3 +' (id,body) VALUES ('''+ $1 +''','''+ $2 +''')';

but it still will not work. "ERROR: 42883: operator does not exist: \"unknown\" + character varying"

Pete

On 3/6/07, Jasbinder Singh Bali < jsbali@gmail.com> wrote:

sql := 'INSERT INTO '+ $3 +' (id,body) VALUES ('+ $1 +','+ $2 +')';
remove the double quotes with id and body and see if it works.
~Jas

Re: help with dynamic table name

From

"Jasbinder Singh Bali"

Date:

06 March 2007, 01:22:42

:)..

have fun

On 3/6/07, Peter Schonefeld <peter.schonefeld@gmail.com> wrote:

Got it!

sql := 'INSERT INTO '|| $3 ||' (id,body) VALUES ('''|| $1 ||''','''|| $2 ||''')';

Thanks all :)
Pete

Re: help with dynamic table name

From

Tom Lane

Date:

06 March 2007, 01:36:29

"Peter Schonefeld" <peter.schonefeld@gmail.com> writes:
> ... but it still will not work. "ERROR: 42883: operator does not exist:
> \"unknown\" + character varying"

You missed the tip that the correct string-concatenation operator is ||
not + ...

            regards, tom lane

Re: help with dynamic table name

From

Michael Fuhr

Date:

06 March 2007, 02:19:29

On Mon, Mar 05, 2007 at 11:48:49PM -0500, Tom Lane wrote:
> but in this particular case there's no point in double-quoting those
> target-column names; it'd be the same to write
>
>   sql := 'INSERT INTO '+ $3 +' (id,body) VALUES ('+ $1 +','+ $2 +')';

And shouldn't the + operators be || ?  I'm not seeing + defined for
any character types.

--
Michael Fuhr