Обсуждение: help with dynamic table name

Поиск

Список

Период

Сортировка

help with dynamic table name

От

"Peter Schonefeld"

Дата:

06 марта 2007 г., 02: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

От

"Jasbinder Singh Bali"

Дата:

06 марта 2007 г., 03: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

От

"Peter Schonefeld"

Дата:

06 марта 2007 г., 03: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

От

"Jasbinder Singh Bali"

Дата:

06 марта 2007 г., 03: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

От

"Jasbinder Singh Bali"

Дата:

06 марта 2007 г., 03: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

От

Tom Lane

Дата:

06 марта 2007 г., 03: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

От

"Jasbinder Singh Bali"

Дата:

06 марта 2007 г., 03: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

От

Stephan Szabo

Дата:

06 марта 2007 г., 04: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

От

"Peter Schonefeld"

Дата:

06 марта 2007 г., 04:18:02

Got it!

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

Thanks all :)
Pete

Re: help with dynamic table name

От

"Peter Schonefeld"

Дата:

06 марта 2007 г., 04: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

От

"Jasbinder Singh Bali"

Дата:

06 марта 2007 г., 04: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

От

Tom Lane

Дата:

06 марта 2007 г., 04: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

От

Michael Fuhr

Дата:

06 марта 2007 г., 05: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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: help with dynamic table name