Обсуждение: plperl syntax question

I am trying to run a query in plperl that uses a Perl variable from the
same function.

Here is the code snippet:

my $stmt = qq("SELECT employee_key from employee where id = '$user' ;");
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query('$stmt');

Here is the runtime output:

NOTICE:  stmt = "SELECT employee_key from employee where id = 'stan' ;"
ERROR:  syntax error at or near "$" at line 22.

As you can see, the statement seems correctly formatted, right?

What am I doing wrong here?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

> On Mar 12, 2020, at 4:34 PM, stan <stanb@panix.com> wrote:
> 
> I am trying to run a query in plperl that uses a Perl variable from the
> same function.
> 
> Here is the code snippet:
> 
> my $stmt = qq("SELECT employee_key from employee where id = '$user' ;");
> elog( NOTICE, "stmt = $stmt" );
> my $rv3 = spi_exec_query('$stmt');
> 
> Here is the runtime output:
> 
> NOTICE:  stmt = "SELECT employee_key from employee where id = 'stan' ;"
> ERROR:  syntax error at or near "$" at line 22.
> 
> As you can see, the statement seems correctly formatted, right?
> 
> What am I doing wrong here?
> 
> -- 
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>                         -- Benjamin Franklin
> 
?try
 my $rv3 = spi_exec_query($stmt);
> 
> 

my $rv3 = spi_exec_query('$stmt');
What am I doing wrong here?

On Thu, Mar 12, 2020 at 06:00:01PM -0600, Rob Sargent wrote:
> 
> 
> > On Mar 12, 2020, at 5:16 PM, stan <stanb@panix.com> wrote:
> > 
> > On Thu, Mar 12, 2020 at 04:58:08PM -0600, Rob Sargent wrote:
> >> 
> >> 
> >>> On Mar 12, 2020, at 4:49 PM, stan <stanb@panix.com> wrote:
> >>> 
> >>> On Thu, Mar 12, 2020 at 04:45:58PM -0600, Rob Sargent wrote:
> >>>> 
> >>>> 
> >>>>> On Mar 12, 2020, at 4:34 PM, stan <stanb@panix.com> wrote:
> >>>>> 
> >>>>> I am trying to run a query in plperl that uses a Perl variable from the
> >>>>> same function.
> >>>>> 
> >>>>> Here is the code snippet:
> >>>>> 
> >>>>> my $stmt = qq("SELECT employee_key from employee where id = '$user' ;");
> >>>>> elog( NOTICE, "stmt = $stmt" );
> >>>>> my $rv3 = spi_exec_query('$stmt');
> >>>>> 
> >>>>> Here is the runtime output:
> >>>>> 
> >>>>> NOTICE:  stmt = "SELECT employee_key from employee where id = 'stan' ;"
> >>>>> ERROR:  syntax error at or near "$" at line 22.
> >>>>> 
> >>>>> As you can see, the statement seems correctly formatted, right?
> >>>>> 
> >>>>> What am I doing wrong here?
> >>>>> 
> >>>>> -- 
> >>>>> "They that would give up essential liberty for temporary safety deserve
> >>>>> neither liberty nor safety."
> >>>>>                         -- Benjamin Franklin
> >>>>> 
> >>>> ?try
> >>>> my $rv3 = spi_exec_query($stmt);
> >>> 
> >>> OK, now  I have:
> >>> 
> >>> my $stmt = qq("SELECT employee_key from employee where id = '$user' ;");
> >>> elog( NOTICE, "stmt = $stmt" );
> >>> my $rv3 = spi_exec_query($stmt);
> >>> 
> >>> And the runtime output is now:
> >>> 
> >>> NOTICE:  stmt = "SELECT employee_key from employee where id = 'stan' ;"
> >>> ERROR:  syntax error at or near ""SELECT employee_key from employee where
> >>> id = 'stan' ;"" at line 22.
> >>> 
> >> 
> >> Change you double quotes to single around you select stmt.  $user probably doesn???t need quotes at all
> >> 
> >>> Looks like somehow that is createing a double set of quotes
> >>> -- 
> >>> "They that would give up essential liberty for temporary safety deserve
> >>> neither liberty nor safety."
> >>>                         -- Benjamin Franklin
> >> 
> > Thanks.
> > 
> > my $stmt = qq('SELECT employee_key from employee where id = $user ;');
> > elog( NOTICE, "stmt = $stmt" );
> > my $rv3 = spi_exec_query($stmt);
> > 
> > runtimeL
> > 
> > NOTICE:  stmt = 'SELECT employee_key from employee where id = stan ;'
> > ERROR:  syntax error at or near "'SELECT employee_key from employee where
> > id = stan ;'" at line 22.
> > CONTEXT:  PL/Perl function "a_foo"
> > 
> > This is not normally hard to get working in non plperl.
> > 
> Sorry, I didn???t know qq was adding surrounding double quotes.
> You may need something like 
> qq(SELECT employee_key from employee where id = ') + $user + qq(';);
> or
> qq(SELECT employee_key from employee where id = '$user???;);
> 
> depending on how qq works. 
> 

I will play with it, but i am not optimistic. You see the elog, that prints
the string that comes out of qq, right? qq is for quoting strings to be
passed to DBD/DBI calls.

I cannot figure out why the call in plpwel is so strange. I would love to
see a workign example of a call to this fucntion that uses a variable, and
works.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On Thu, Mar 12, 2020 at 06:37:02PM -0700, David G. Johnston wrote:
> On Thursday, March 12, 2020, stan <stanb@panix.com> wrote:
> >
> > my $rv3 = spi_exec_query('$stmt');
> > What am I doing wrong here?
> >
> 
> Putting a variable name into a single-quoted string and expecting it to
> resolve to the contents of said variable instead of being treated as a
> literal.
> David J.

Please look at this.

Here is the code:

my $stmt = qq('SELECT employee_key from employee where id = $user ;');
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query($stmt);

As you cna see, I use qq to ahndle the qouting: runtime output looks like
this:

OTICE:  stmt = 'SELECT employee_key from employee where id = stan ;'
ERROR:  syntax error at or near "$" at line 22.

Looks like a very clean string with the variable substituted.

Anyone have a working example of a call to spi_exec_query() using a variable?

also tried:

my $rv3 = spi_exec_query('$stmt');

Different trun tiem error message.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On Mar 12, 2020, at 7:44 PM, stan <stanb@panix.com> wrote:

On Thu, Mar 12, 2020 at 06:37:02PM -0700, David G. Johnston wrote:

On Thursday, March 12, 2020, stan <stanb@panix.com> wrote:

my $rv3 = spi_exec_query('$stmt');
What am I doing wrong here?

Putting a variable name into a single-quoted string and expecting it to
resolve to the contents of said variable instead of being treated as a
literal.
David J.

Please look at this.

Here is the code:

my $stmt = qq('SELECT employee_key from employee where id = $user ;');
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query($stmt);

The example I saw for qq on the perl site 

print(qq(Welcome to GeeksForGeeks));

doesn’t have any quotes in arg to qq

Try making the sql string without using qq

my $select = “select....’” + $user + “‘;”;

(or perhaps perl has a formatted string function like printf)