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.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Hi,

Am Mi, den 18.08.2004 schrieb Grainne Reilly um 6:22:[color=blue]
> 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.[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Wed, Aug 18, 2004 at 12:22:27AM -0400, Grainne Reilly wrote:[color=blue]
> I am new to PostgreSQL and am porting some scripts written for MySQL
> over to psql.[/color]

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. :)
[color=blue]
> 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;[/color]

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.
[color=blue]
> Thanks in advance for any suggestions,[/color]

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

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:[color=blue]
>On Wed, Aug 18, 2004 at 12:22:27AM -0400, Grainne Reilly wrote:[color=green]
> > I am new to PostgreSQL and am porting some scripts written for MySQL
> > over to psql.[/color]
>
>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. :)
>[color=green]
> > 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;[/color]
>
>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.
>[color=green]
> > Thanks in advance for any suggestions,[/color]
>
>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[/color]



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

What do you mean varables do not exists?
Ever heard of SELECT INTO?


Grainne Reilly wrote:[color=blue]
> 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:
>[color=green]
>> On Wed, Aug 18, 2004 at 12:22:27AM -0400, Grainne Reilly wrote:[color=darkred]
>> > I am new to PostgreSQL and am porting some scripts written for MySQL
>> > over to psql.[/color]
>>
>> 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. :)
>>[color=darkred]
>> > 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;[/color]
>>
>> 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.
>>[color=darkred]
>> > Thanks in advance for any suggestions,[/color]
>>
>> 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[/color]
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wed, Aug 18, 2004 at 04:45:57PM -0400, Jean-Luc Lachance wrote:[color=blue]
> What do you mean varables do not exists? Ever heard of SELECT INTO?[/color]

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
[color=blue]
> Grainne Reilly wrote:[color=green]
> >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:
> >[color=darkred]
> >>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[/color]
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >[/color][/color]

--
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 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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
[color=blue]
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)