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 6 12735
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
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 da***@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: 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 da***@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
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 da***@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
---------------------------(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: 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 da***@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 da***@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rob Sell |
last post by:
Greetings all,
Yesterday I upgraded from 7.3 to 7.4 now psql doesn't work! I get the
following error.
psql: relocation error: psql: undefined symbol: get_progname
Any ideas out there?
Rob
|
by: Willem Herremans |
last post by:
I am developing a client application for postgreSQL in Tcl/Tk (see
http://gborg.postgresql.org/project/pfm ).
It mainly uses PgTcl or pgintcl. I don't have any problems with those,
but I am also...
|
by: Brendan Jurd |
last post by:
Hello all,
I just wanted to pass on some information about compatibility between
the psql client and the postgres server.
On a particular network, my workstation and the server are both debian...
|
by: Ben |
last post by:
I'm designing a fairly involved database system. As part fo the process, I
use the \i command a great deal. I set up fairly involved queries,
sometimes simply for the purpose of shortening column...
|
by: Leo Leo |
last post by:
Hi!
How can I interpret a variable in psql, when the variable has to be quoted?
for example:
\set myVar myValue
\echo :myVar
The Result ist then "myValue" ==> ok
|
by: Dino Vliet |
last post by:
Hi folks,
probably this is a question you've heard so many times
but I wasn't able to find a solution to it.
I'm using a shell script to create a textfile for me.
It looks like...
|
by: Josué Maldonado |
last post by:
Hello List,
I'm having this issue with beta 8.0
C:\pgsql\bin>pg_dump -U postgres farmacia > xfar.sql
Password:
C:\pgsql\bin>psql -U postgres farmacia2 < xfar.sql
Password:
psql: FATAL: ...
|
by: Ron St-Pierre |
last post by:
Is there a way 'within' psql to suppress output?
One of our cron scripts calls a sql file which contains various database
commands (ALTER TABLEs, UPDATEs, etc) and various user-defined functions....
|
by: John Sidney-Woollett |
last post by:
With the advent of postgres v8, would it be possible to change the
default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?
Although this might break backward compatibility, it might be...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |