Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Set-Returning Functions WAS: On the performance of views

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Bill Moran <wmoran(at)potentialtech(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [PERFORM] Set-Returning Functions WAS: On the performance of views
Date: 2004-01-29 19:21:54
Message-ID: 200401291121.54048.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
Bill,

First off:  discussion moved to the SQL list, where it really belongs.

> Well, I would have agreed with the uselessness, until this project.  The
> "source of endless debugging" frightens me!

Well, the last time I tried to use this capability was SQL Server 7.   On that 
model, the problems I found were:
1) There was no good way to differentiate the recordsets returned; you had to 
keep careful track of what order they were in and put in "fillers" for 
recordsets that didn't get returned. 
2) Most of the MS client technology (ODBC, ADO) was not prepared to handle 
multiple recordsets.  I ended up hiring a C-based COM hacker to write me a 
custom replacement for ADO so that we could handle procedure results 
reliably.

All in all, it wasn't worth it and if I had the project to do over again, I 
would have chosen a different approach.

> > This can be done with Set Returning Functions.   The issue is that the
> > call to the function requires special syntax, and the program calling the
> > function must know what columns are going to be returned at the time of
> > the call. Hmmm, is that clear or confusing?
>
> Clear as mud.  In my case, my application simply doesn't care what row of
> what kind are returned.  See, I'm writing the server end, and all said and
> done, it's really just glue (frighteningly thick glue, but glue
> nonetheless)

To be clearer:  You can create a Set Returning Function (SRF) without a 
clearly defined set of return columns, and just have it return "SETOF 
RECORD".   However, when you *use* that function, the query you use to call 
it needs to have a clear idea of what columns will be returned, or you get no 
data.

All of this is very hackneyed, as I'm sure you realize.   Overall, I'd say 
that the programming team you've been inflicted with don't like relational 
databases, or at least have no understanding of them.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

pgsql-performance by date

Next:From: lndDate: 2004-01-29 19:29:59
Subject: Explain plan for 2 column index
Previous:From: Bill MoranDate: 2004-01-29 19:12:14
Subject: Re: On the performance of views

pgsql-sql by date

Next:From: Bill MoranDate: 2004-01-29 19:44:28
Subject: Re: [PERFORM] Set-Returning Functions WAS: On the performance of
Previous:From: Bill MoranDate: 2004-01-29 19:12:14
Subject: Re: On the performance of views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group