Обсуждение: Column names in rowsets returned from function

Hi,

I have a question - is it possible to rename
columns in rowset returned from plpgsql function
delared as 'returns table(...)' ?

It seems it's not possible, even using
'as "column_name"' in internal query and
column names are always the same as in
'table(...)' definition.

If not, could it be possible in the future ?

Thanks,
Krzysztof

Krzysztof Barlik <kbarlik@wp.pl> writes:
> I have a question - is it possible to rename
> columns in rowset returned from plpgsql function
> delared as 'returns table(...)' ?

Sure ...

regression=# create function foo() returns table (a int, b text) as
regression-# $$ values (1,'one'), (2,'two') $$ language sql;
CREATE FUNCTION
regression=# select * from foo();
 a |  b
---+-----
 1 | one
 2 | two
(2 rows)

regression=# select * from foo() as x(y,z);
 y |  z
---+-----
 1 | one
 2 | two
(2 rows)


If that's not what you are talking about, you need to be more
specific about what you are talking about.

            regards, tom lane

Hi Tom,

> If that's not what you are talking about, you need to be more
> specific about what you are talking about.

you are right, I was not specific enough.

I meant a situation where returned rowset
may have different set of column names depending
on data in the database, not on explicit user request.

Regards,
Krzysztof

2009/9/26 Krzysztof Barlik <kbarlik@wp.pl>:
> Hi Tom,
>
>> If that's not what you are talking about, you need to be more
>> specific about what you are talking about.
>
> you are right, I was not specific enough.
>
> I meant a situation where returned rowset
> may have different set of column names depending
> on data in the database, not on explicit user request.


no this isn't possible yet

regards
Pavel Stehule
>
> Regards,
> Krzysztof
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Saturday 26 September 2009 12:49:03 pm Krzysztof Barlik wrote:
> Hi Tom,
>
> > If that's not what you are talking about, you need to be more
> > specific about what you are talking about.
>
> you are right, I was not specific enough.
>
> I meant a situation where returned rowset
> may have different set of column names depending
> on data in the database, not on explicit user request.
>
> Regards,
> Krzysztof

Just to make things clear, is this the same question and situation that Jerzy
Bialas brought up in another post?

http://archives.postgresql.org/pgsql-general/2009-09/msg00922.php

--
Adrian Klaver
aklaver@comcast.net

> Just to make things clear, is this the same question and situation that
> Jerzy Bialas brought up in another post?
>
> http://archives.postgresql.org/pgsql-general/2009-09/msg00922.php

Yes, I didn't know he posted it earlier.
Sorry for the confusion and thanks for response.

Regards,
Krzysztof

Hi,

 is it possible to force pg_dumpall to ask for
password only once (connecting as superuser 'postgres') ?

Entering it for every database is a bit annoying.

Thanks,
Krzysztof

On Tue, 2009-10-06 at 22:59 +0200, Krzysztof Barlik wrote:
> Hi,
>
>  is it possible to force pg_dumpall to ask for
> password only once (connecting as superuser 'postgres') ?
>
> Entering it for every database is a bit annoying.

take a look at .pgpass documentation

>
> Thanks,
> Krzysztof
>
>
>
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

Krzysztof Barlik wrote:
> Hi,
>
>  is it possible to force pg_dumpall to ask for
> password only once (connecting as superuser 'postgres') ?
>
> Entering it for every database is a bit annoying.

What version are you running?  IIRC it should remember the password
between databases.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, 2009-10-06 at 22:59 +0200, Krzysztof Barlik wrote:
> Hi,
>
>  is it possible to force pg_dumpall to ask for
> password only once (connecting as superuser 'postgres') ?
>
> Entering it for every database is a bit annoying.

take a look at .pgpass documentation

>
> Thanks,
> Krzysztof
>
>
>
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

> What version are you running?  IIRC it should remember the password
> between databases.

8.4.0 on Linux/x86_64. It does not, and man page clearly says:

"pg_dumpall  needs  to  connect  several  times  to  the
PostgreSQL  server (once per database). If you use password
authentication it will ask for a password  each  time."

Is there any specific reason for this behaviour ?

Thanks for Scott and Joshua - I read documentation about
.pgpass and PGPASSWORD, I just thought it would be a bit
more convenient and possibly safer to do it without
storing password in files or environment variables.

Thanks,
Krzysztof