Re: Output of PL/PGSQL?

On May 4, 2005, at 11:39 AM, David wrote:

> Is it possible to create output from a PL/PGSQL function that looks
> like the output of a query? (And how do you do it?)
>
> I want to write dynamic SQL based on some inputs from a web form and
> then hand the query results back to the web app. I'm using
> Zope/Psycopg on the web layer and it does the mapping of the query
> columns to variables in Zope for me, when running plain queries. But a
> PL/PGSQL function by default always creates a result with "one column"
> (named after the function).

I'm not familiar with Zope/Psycopg, but in general a set returning
function does return multiple columns. Here is an example:

create type web.item_count as (count integer, item text);

create or replace function web.download_count (dname text, timeframe
interval)
returns setof web.item_count as $$
    select * from web.url_count_downloads($1, $2, '{"%.dmg", "%.exe",
"%.msi"}')
        order by 1 desc;
$$ language sql;



>
> I have an idea how I can format the resulting rows, but I could not
> find how I could change the "header". Assuming I can customise the
> header, is there a standard way to output also the resulting rows as
> if they come from a plain query? (Plain query being a SELECT FROM
> statement, instead of a FUNCTION call.)

You can use the "as" construct to rename the output header:

select now() as "This is the current time";
    This is the current time
-------------------------------
  2005-05-04 15:01:33.278644-04
(1 row)

Or in the example above the columns (because of the type) would be
"count" and "item". So to change column names this the call would look
like this:

select count as "Col1 Name", item as "Col2 Name" from
web.download_count('pgedit.com', '24 hours');


>
> Also, would a PL/PGSQL function necessarily be a lot slower then a
> plain query? (I could write a bunch of queries to cover all possible
> input from the form and use Zope to decided which one to run, but this
> looks less elegant to me.)

Probably not slower and could be faster since some of the execution
steps (and even the results depending on the function) can be cached.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL