Thread: psql - user defined SQL variables

psql - user defined SQL variables

From
Grainne Reilly
Date:
I am new to PostgreSQL and am porting some scripts written for MySQL over
to psql.  There is one MySQL feature which I cannot find in psql - user
defined SQL variables. In MySQL I can use these to hold the result
(numeric, string or null) of a select: e.g.
select @count = count(*) from mytable;
The @count variable now holds the result of that select, and I can use it
in where clauses, updates, inserts  etc.
I checked out the \set psql variable but I haven't found a way to tweak it
to give me the result of a sql statement - is there any way to do that (I'm
using PostgreSQL version 7.4.3)?
Thanks in advance for any suggestions,
Grainne.



Re: psql - user defined SQL variables

From
Tino Wildenhain
Date:
Hi,

Am Mi, den 18.08.2004 schrieb Grainne Reilly um 6:22:
> I am new to PostgreSQL and am porting some scripts written for MySQL over
> to psql.  There is one MySQL feature which I cannot find in psql - user
> defined SQL variables. In MySQL I can use these to hold the result
> (numeric, string or null) of a select: e.g.
> select @count = count(*) from mytable;
> The @count variable now holds the result of that select, and I can use it
> in where clauses, updates, inserts  etc.
> I checked out the \set psql variable but I haven't found a way to tweak it
> to give me the result of a sql statement - is there any way to do that (I'm
> using PostgreSQL version 7.4.3)?
> Thanks in advance for any suggestions,
> Grainne.

Postgres supports subselects (for update and stuff) and you can insert
like that:

INSERT INTO foo (a,b,c) SELECT a,b,c FROM ...

If thats not enough, you have a bounch of pl/*
languages to write sophisticated procedures
in the database where you have variables for
single values as well as recordsets.

Regards
Tino Wildenhain


Re: psql - user defined SQL variables

From
David Fetter
Date:
On Wed, Aug 18, 2004 at 12:22:27AM -0400, Grainne Reilly wrote:
> I am new to PostgreSQL and am porting some scripts written for MySQL
> over to psql.

The least work you'll do here is to refactor your app entirely.

PostgreSQL will just plain handle about 3/4 of what you used to have
to deal with up in middleware land.  It also supports all kinds of
wizardry that will astound you as you run across it. :)

> There is one MySQL feature which I cannot find in psql - user
> defined SQL variables. In MySQL I can use these to hold the result
> (numeric, string or null) of a select: e.g.  select @count =
> count(*) from mytable;

This is a MySQLism to work around their lack of subselects.  But if
that turns out not to be enough, you have a broad choice of procedural
languages, from the ADA-like PL/PgSQL to PL/Perl, PL/Python and (soon)
PL/PHP.

> Thanks in advance for any suggestions,

See about redoing your app.  It will be less work than trying to graft
the MySQL design onto PostgreSQL, which is about like trying to hook
an ox cart to a jet engine :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: psql - user defined SQL variables

From
Grainne Reilly
Date:
Thanks for the response - pity PostgreSQL doesn't have this. These are a
bunch of quick and dirty data population scripts which I was hoping to
avoid rewriting to use temporary tables, subselects etc.
It is a useful feature for these kind of scripts (Sybase has it and it has
always had subselects) - and in Oracle I can use an anonymous PL/SQL block
to declare and use variables directly in sqlplus.
Are there any plans for PostgreSQL to support anonymous PL/pgSQL blocks
directly in psql - now that would be very nice!
Thanks again.
Grainne.


At 01:48 AM 8/18/2004, David Fetter wrote:
>On Wed, Aug 18, 2004 at 12:22:27AM -0400, Grainne Reilly wrote:
> > I am new to PostgreSQL and am porting some scripts written for MySQL
> > over to psql.
>
>The least work you'll do here is to refactor your app entirely.
>
>PostgreSQL will just plain handle about 3/4 of what you used to have
>to deal with up in middleware land.  It also supports all kinds of
>wizardry that will astound you as you run across it. :)
>
> > There is one MySQL feature which I cannot find in psql - user
> > defined SQL variables. In MySQL I can use these to hold the result
> > (numeric, string or null) of a select: e.g.  select @count =
> > count(*) from mytable;
>
>This is a MySQLism to work around their lack of subselects.  But if
>that turns out not to be enough, you have a broad choice of procedural
>languages, from the ADA-like PL/PgSQL to PL/Perl, PL/Python and (soon)
>PL/PHP.
>
> > Thanks in advance for any suggestions,
>
>See about redoing your app.  It will be less work than trying to graft
>the MySQL design onto PostgreSQL, which is about like trying to hook
>an ox cart to a jet engine :)
>
>Cheers,
>D
>--
>David Fetter david@fetter.org http://fetter.org/
>phone: +1 510 893 6100   mobile: +1 415 235 3778
>
>Remember to vote!
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match



Re: psql - user defined SQL variables

From
Jean-Luc Lachance
Date:
What do you mean varables do not exists?
Ever heard of SELECT INTO?


Grainne Reilly wrote:
> Thanks for the response - pity PostgreSQL doesn't have this. These are a
> bunch of quick and dirty data population scripts which I was hoping to
> avoid rewriting to use temporary tables, subselects etc.
> It is a useful feature for these kind of scripts (Sybase has it and it
> has always had subselects) - and in Oracle I can use an anonymous PL/SQL
> block to declare and use variables directly in sqlplus.
> Are there any plans for PostgreSQL to support anonymous PL/pgSQL blocks
> directly in psql - now that would be very nice!
> Thanks again.
> Grainne.
>
>
> At 01:48 AM 8/18/2004, David Fetter wrote:
>
>> On Wed, Aug 18, 2004 at 12:22:27AM -0400, Grainne Reilly wrote:
>> > I am new to PostgreSQL and am porting some scripts written for MySQL
>> > over to psql.
>>
>> The least work you'll do here is to refactor your app entirely.
>>
>> PostgreSQL will just plain handle about 3/4 of what you used to have
>> to deal with up in middleware land.  It also supports all kinds of
>> wizardry that will astound you as you run across it. :)
>>
>> > There is one MySQL feature which I cannot find in psql - user
>> > defined SQL variables. In MySQL I can use these to hold the result
>> > (numeric, string or null) of a select: e.g.  select @count =
>> > count(*) from mytable;
>>
>> This is a MySQLism to work around their lack of subselects.  But if
>> that turns out not to be enough, you have a broad choice of procedural
>> languages, from the ADA-like PL/PgSQL to PL/Perl, PL/Python and (soon)
>> PL/PHP.
>>
>> > Thanks in advance for any suggestions,
>>
>> See about redoing your app.  It will be less work than trying to graft
>> the MySQL design onto PostgreSQL, which is about like trying to hook
>> an ox cart to a jet engine :)
>>
>> Cheers,
>> D
>> --
>> David Fetter david@fetter.org http://fetter.org/
>> phone: +1 510 893 6100   mobile: +1 415 235 3778
>>
>> Remember to vote!
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if
>> your
>>       joining column's datatypes do not match
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

Re: psql - user defined SQL variables

From
David Fetter
Date:
On Wed, Aug 18, 2004 at 04:45:57PM -0400, Jean-Luc Lachance wrote:
> What do you mean varables do not exists?  Ever heard of SELECT INTO?

Yes, and for variable assignment, it works inside stored procedures,
but not elsewhere.

Oh, and please to be nice to the people who ask questions.  :)

Cheers,
D

> Grainne Reilly wrote:
> >Thanks for the response - pity PostgreSQL doesn't have this. These are a
> >bunch of quick and dirty data population scripts which I was hoping to
> >avoid rewriting to use temporary tables, subselects etc.
> >It is a useful feature for these kind of scripts (Sybase has it and it
> >has always had subselects) - and in Oracle I can use an anonymous PL/SQL
> >block to declare and use variables directly in sqlplus.
> >Are there any plans for PostgreSQL to support anonymous PL/pgSQL blocks
> >directly in psql - now that would be very nice!
> >Thanks again.
> >Grainne.
> >
> >
> >At 01:48 AM 8/18/2004, David Fetter wrote:
> >
> >>On Wed, Aug 18, 2004 at 12:22:27AM -0400, Grainne Reilly wrote:
> >>> I am new to PostgreSQL and am porting some scripts written for MySQL
> >>> over to psql.
> >>
> >>The least work you'll do here is to refactor your app entirely.
> >>
> >>PostgreSQL will just plain handle about 3/4 of what you used to have
> >>to deal with up in middleware land.  It also supports all kinds of
> >>wizardry that will astound you as you run across it. :)
> >>
> >>> There is one MySQL feature which I cannot find in psql - user
> >>> defined SQL variables. In MySQL I can use these to hold the result
> >>> (numeric, string or null) of a select: e.g.  select @count =
> >>> count(*) from mytable;
> >>
> >>This is a MySQLism to work around their lack of subselects.  But if
> >>that turns out not to be enough, you have a broad choice of procedural
> >>languages, from the ADA-like PL/PgSQL to PL/Perl, PL/Python and (soon)
> >>PL/PHP.
> >>
> >>> Thanks in advance for any suggestions,
> >>
> >>See about redoing your app.  It will be less work than trying to graft
> >>the MySQL design onto PostgreSQL, which is about like trying to hook
> >>an ox cart to a jet engine :)
> >>
> >>Cheers,
> >>D
> >>--
> >>David Fetter david@fetter.org http://fetter.org/
> >>phone: +1 510 893 6100   mobile: +1 415 235 3778
> >>
> >>Remember to vote!
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 9: the planner will ignore your desire to choose an index scan if
> >>your
> >>      joining column's datatypes do not match
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: Have you searched our list archives?
> >
> >              http://archives.postgresql.org
> >

--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: psql - user defined SQL variables

From
Chris Travers
Date:
Can you use a temporary table for this?

You could wrap it in a stored proc (say SQL language) for getting and
setting varaibles.

Best Wishes,
Chris Travers
Metatron Technology COnsulting

>


Attachment