Re: plperl syntax question

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