Обсуждение: Minor systax error but not able to resolve it...
Hi, could anyone please help me to sort out below error. I have spent lot of time but couldn't resolved it.
mydb=> CREATE OR REPLACE FUNCTION test_create()
RETURNS void AS
$BODY$
$cmd = "CREATE TABLE test-table(col varchar not null);";
spi_exec_query("CREATE OR REPLACE FUNCTION my_tmp_func() RETURNS void AS $cmd LANGUAGE sql;");
$BODY$
LANGUAGE 'plperl';
CREATE FUNCTION
mydb=> SELECT test_create();
ERROR: error from Perl function "test_create": syntax error at or near "CREATE" at line 3.
mydb=>
RETURNS void AS
$BODY$
$cmd = "CREATE TABLE test-table(col varchar not null);";
spi_exec_query("CREATE OR REPLACE FUNCTION my_tmp_func() RETURNS void AS $cmd LANGUAGE sql;");
$BODY$
LANGUAGE 'plperl';
CREATE FUNCTION
mydb=> SELECT test_create();
ERROR: error from Perl function "test_create": syntax error at or near "CREATE" at line 3.
mydb=>
I want to create function from my stored procedure in any language.
Thanks,
Dipti
Richard Huxton <dev@archonet.com> writes: > You're interpolating $cmd here but not quoting it, so you end up with: > ... RETURNS void AS CREATE TABLE test-table... > whereas you want: > ... RETURNS void AS 'CREATE TABLE test-table...'... > Probably best to use dollar-quoting: $TMP$ or similar, but don't forget > to escapt the $ or perl will think it's a variable. Dollar-quoting is really dangerous when you're trying to quote nonconstant text. I'd suggest passing the string through quote_literal() instead. regards, tom lane
Thanks. Putting $cmd in single quote resolve the error but it generated other error. :(
mydb=> CREATE OR REPLACE FUNCTION test_create()
RETURNS void AS
$BODY$
$cmd = "CREATE TABLE testtable(col varchar not null);";
spi_exec_query("CREATE OR REPLACE FUNCTION myfunc() RETURNS void AS '$cmd' LANGUAGE plperl;");
spi_exec_query("SELECT myfunc();");
$BODY$
LANGUAGE 'plperl';
CREATE FUNCTION
mydb=> SELECT test_create();
ERROR: error from Perl function "test_create": error from Perl function "myfunc": Can't locate object method "col" via package "varchar" (perhaps you forgot to load "varchar"?) at line 1. at line 3.
this is also related to quoting?
mydb=> CREATE OR REPLACE FUNCTION test_create()
RETURNS void AS
$BODY$
$cmd = "CREATE TABLE testtable(col varchar not null);";
spi_exec_query("CREATE OR REPLACE FUNCTION myfunc() RETURNS void AS '$cmd' LANGUAGE plperl;");
spi_exec_query("SELECT myfunc();");
$BODY$
LANGUAGE 'plperl';
CREATE FUNCTION
mydb=> SELECT test_create();
ERROR: error from Perl function "test_create": error from Perl function "myfunc": Can't locate object method "col" via package "varchar" (perhaps you forgot to load "varchar"?) at line 1. at line 3.
this is also related to quoting?
On Tue, Feb 23, 2010 at 9:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Richard Huxton <dev@archonet.com> writes:Dollar-quoting is really dangerous when you're trying to quote
> You're interpolating $cmd here but not quoting it, so you end up with:
> ... RETURNS void AS CREATE TABLE test-table...
> whereas you want:
> ... RETURNS void AS 'CREATE TABLE test-table...'...
> Probably best to use dollar-quoting: $TMP$ or similar, but don't forget
> to escapt the $ or perl will think it's a variable.
nonconstant text. I'd suggest passing the string through
quote_literal() instead.
regards, tom lane
Wonderful! Thanks.
On Wed, Feb 24, 2010 at 2:03 AM, Richard Huxton <dev@archonet.com> wrote:
On 23/02/10 17:15, dipti shah wrote:The function "myfunc" you are trying to create as plperl isn't perl. Either give it some perl or try "LANGUAGE SQL".Thanks. Putting $cmd in single quote resolve the error but it generated
other error. :(
mydb=> CREATE OR REPLACE FUNCTION test_create()
RETURNS void AS
$BODY$
$cmd = "CREATE TABLE testtable(col varchar not null);";
spi_exec_query("CREATE OR REPLACE FUNCTION myfunc() RETURNS void AS
'$cmd'LANGUAGE plperl;");
spi_exec_query("SELECT myfunc();");
$BODY$
LANGUAGE 'plperl';
CREATE FUNCTION
mydb=> SELECT test_create();
ERROR: error from Perl function "test_create": error from Perl function
"myfunc": Can't locate object method "col" via package "varchar" (perhaps
you forgot to load "varchar"?) at line 1. at line 3.
--
Richard Huxton
Archonet Ltd