[SQL] Returning Recordsets from Stored-procs
Is there anyway to return a recordset from a Stored Procedure in Postgres so that it can be used as a type of view or select? I know that you can do this in Interbase or MS-SQL. I have seen that you can return a complete record but that's not really the same thing. Marc Rohloff
Re: [SQL] Returning Recordsets from Stored-procs
Marc, Marc Rohloff wrote: > Is there anyway to return a recordset from a Stored Procedure in Postgres so that it >can be used as a type of view or select? In short:- No, there isn't. More detail:- PostgreSQL does not have stored procedures as such, it has user defined functions. The difference being that a stored procedure returns both a scalar value (the result), and optionally, one or more sets. (your recordset) Functions on the other hand, only return a scalar value. This has been slightly extended in PostgreSQL so that a list of values can be returned, but these have to be of the same type, and so are not a general replacement for a set. Whilst this is an unfortunate position at the moment, it has been my experience that it does not cause insurmountable problems. (Some short term headaches - yes. ;-) > I know that you can do this in Interbase or MS-SQL. > > I have seen that you can return a complete record but that's not really the same >thing. > > Marc Rohloff Regards, Grant -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
[SQL] Re: Returning Recordsets from Stored-procs
On Mon, 6 Nov 2000, Grant Finnemore wrote: > Whilst this is an unfortunate position at the moment, it has been my experience that >it does not cause insurmountable problems. > (Some short term headaches - yes. ;-) After learning this as a fact you have short term headaches but before you have continuos headache while trying to port a database. I think I'm not the only one who would be really, really happy if *real* stored procedures would be high on top of the todo list. (In fact this is the *only* thing I'm currently really missing in PostgreSQL.) Kind regards Andreas.
Re: [SQL] Re: Returning Recordsets from Stored-procs
Andreas Tille wrote: > On Mon, 6 Nov 2000, Grant Finnemore wrote: > > > Whilst this is an unfortunate position at the moment, it has been my experience >that it does not cause insurmountable problems. > > (Some short term headaches - yes. ;-) > After learning this as a fact you have short term headaches but before > you have continuos headache while trying to port a database. > I think I'm not the only one who would be really, really happy if > *real* stored procedures would be high on top of the todo list. > > (In fact this is the *only* thing I'm currently really missing in > PostgreSQL.) > > Kind regards > > Andreas. I will second Andreas Najm
[SQL] Timing trouble with GROUP BY PostgreSQL / Oracle ?
Hi, I would like to know if someone have a solution for me ... I have a table with today about 2,8 millions records. The table have good indexes, in our case on idcond, and points ! When I do : select sum(points) from gains; With Oracle : 8 sec With PostGreSQL : 10 sec OK for this it's ok ... But when I do : select sum(points) from gains group by idcond; With Oracle : 22 sec With PostGreSQL : about 3 minutes !!! I have done a vacuum analyse of the table gains ... just before testing ... What can I do to optimize that result ? Why the group by function is so slow ?? Thanks for your help ! Regards, -- Hervé Piedvache Elma Ingenierie Informatique 6, rue du Faubourg Saint-Honoré F-75008 - Paris - France http://www.elma.fr Tel: +33-1-44949901 Fax: +33-1-44949902 Email: [EMAIL PROTECTED]
[SQL] UNION in views
Hi all, I am part of the team that's porting the ArsDigita Community System (ACS), a toolkit to create community-oriented db-backed websites, from Oracle to PostgreSQL. We call the projet OpenACS. We are thinking of using PG 7.1b for the port (since it'll take some time, allowing PG 7.1 to mature), because of OUTER JOINs support and because of a message by Tom Lane that I read a couple days ago where he said that subselects in the from clause are now supported in the CVS version of PG. I was wondering if UNIONs in VIEWS will be supported too. Do you have a time frame for the release of 7.1b? Any additional comments on 7.1 features,drawbacks, caveats, etc, are appreciated. Thanks, -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu http://www.brasileiro.net/roberto
[SQL] SQL question regarding a couple of table joins.
Greetings.
We three have three tables, a links table which stores basic information
about a link, a linksdetail table which stores more detailed information
about the link, and a linkdaystats table which records the daily statistical
information on the link.
My problem is, once a day I want to delete everything in that table to start
afresh. Any information accumulated in the linkdaystats table has been
parsed, multilated, spindled, whatever, and done with, do I merely do a
'drop from linkdaystats' to start afresh. The only issue is this causes
other queries to break, suck as the only below :
SELECT
links.linkid,
links.linkurl,
links.linktext,
links.bannerid,
linkdaystats.linkid
FROM
links,
linkdetail ,
linkdaystats
WHERE
links.linkid = linkdetail.linkid
linkid | linkurl | linktext | bannerid | linkid
+-+--+--+
(0 rows)
Help? :) This is PostGreSQL 7.0.2 on Debian 2.2/Linux-2.2.17 (Kernel/PGSQL
from source)
--- Table information :
freehost=# \d links
Table "links"
Attribute | Type | Modifier
---+--+
linkid| integer | not null default nextval('links_linkid_seq'::text)
linkurl | varchar(255) |
linktext | varchar(255) |
bannerid | integer |
Index: links_linkid_key
freehost=# \d linkdetail
Table "linkdetail"
Attribute | Type | Modifier
---+--+--
linkid| integer |
referrer | varchar(255) |
maxclicks | integer |
maximpressions| integer |
primarycategory | integer |
secondarycategory | integer |
tertiarycategory | integer |
weight| float4 |
starttime | timestamp|
stoptime | timestamp|
freehost=# \d linkdaystats
Table "linkdaystats"
Attribute | Type| Modifier
-+---+--
linkid | integer |
datestamp | timestamp |
clicks | bigint|
impressions | bigint|
Sincerely, Warren
Re: [SQL] UNION in views
Roberto Mello <[EMAIL PROTECTED]> writes: > I was wondering if UNIONs in VIEWS will be supported too. Already there for 7.1. regards, tom lane
