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(at)panix(dot)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(at)panix(dot)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(at)panix(dot)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