Postgres Pro
Downloads
Home   >   mailing lists

Thread: Doing a \set through perl DBI ?

Doing a \set through perl DBI ?

From
David Gauthier
Date:
Hi:

Is there a way to do the equivalent of a "\set foo 1" through perl dbi ?
I tried... 
    $dbh->do("\\set foo 1");
and got a syntax error

Of course, I'd also have to be able to access the value of foo once its set.   I'm guessing the usual way ??? (select :foo)


Thanks for any help !

Re: Doing a \set through perl DBI ?

From
"David G. Johnston"
Date:
On Wednesday, June 6, 2018, David Gauthier <davegauthierpg@gmail.com> wrote:
Hi:

Is there a way to do the equivalent of a "\set foo 1" through perl dbi ?
I tried... 
    $dbh->do("\\set foo 1");
and got a syntax error

Of course, I'd also have to be able to access the value of foo once its set.   I'm guessing the usual way ??? (select :foo)


No.  Meta commands are psql client program only.  You'd need to explain why you want this to get reasonable server-side suggestions.

David J.
 

Re: Doing a \set through perl DBI ?

From
David Gauthier
Date:
I think I found my own answer.  I wanted to use the current linux user's uid as part of a query (again, this is a perl/DBI script).  I was thinking I might be able to set a variable into the DB session somehow using \set through DBI to accomplish this.  The solution that finally occurred to me was to create a temporary table and have the perl script shove the uid into a column in that table, then query against that.  Sort of like using a temp table to store variables set from outside. 

On Wed, Jun 6, 2018 at 11:46 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, June 6, 2018, David Gauthier <davegauthierpg@gmail.com> wrote:
Hi:

Is there a way to do the equivalent of a "\set foo 1" through perl dbi ?
I tried... 
    $dbh->do("\\set foo 1");
and got a syntax error

Of course, I'd also have to be able to access the value of foo once its set.   I'm guessing the usual way ??? (select :foo)


No.  Meta commands are psql client program only.  You'd need to explain why you want this to get reasonable server-side suggestions.

David J.
 

Re: Doing a \set through perl DBI ?

From
Francisco Olarte
Date:
David:

On Wed, Jun 6, 2018 at 5:36 PM, David Gauthier <davegauthierpg@gmail.com> wrote:
> Hi:
>
> Is there a way to do the equivalent of a "\set foo 1" through perl dbi ?
> I tried...
>     $dbh->do("\\set foo 1");
> and got a syntax error
>
> Of course, I'd also have to be able to access the value of foo once its set.
> I'm guessing the usual way ??? (select :foo)

You are looking at it wrong. Psql vars are similar to perl vars, and
it is psql ( the program in your machine ) who interpolates them
before sending the query to the server.

The equivalent perl code to

\set foo 1
select :foo;

Would be

my $foo = '1';
$dbh->do("select $foo")

Which is not terribly useful, in perl.

To use the current ( client ) user in a query ( like your next message
states ), you just need

$dbh->do("select * from some_table where user_id_column=$<")

( Of course, you could "use English;" to get $UID, or use placeholders
to avoid injections, that's just an initial pointer )
( or "perldoc perlvar" if you do not know what $< is, and that is
enough perl stuff for a postres list  )

Francisco Olarte