Обсуждение: format() function with string_agg

Поиск
Список
Период
Сортировка

format() function with string_agg

От
Raj Gandhi
Дата:
I'm trying to use format() function with string_agg with Postgres 9.1.9 but getting error - "too few arguments for format"

I have two tables t_message and t_message_args. Table t_message   has 'message' column with text in the format 'Test message first arg is %1$s and second arg  %2$s end-of-message'

              id             | integer           |
              message | character varying |


And the second table t_message_args contains message argument and values 
              id           | integer           |
             arg          | integer           |
             argvalue  | character varying |


Here is the SQL that uses format() function to retrieve formatted message by replacing arg value in the message:
=
select m.id, format(m.message, string_agg(a.argvalue, ',' order by a.arg)
from t_message m, t_message_args a
where m.id = a.id
group by m.id, m.message


ERROR:  too few arguments for format

********** Error **********

ERROR: too few arguments for format
SQL state: 22023





Re: format() function with string_agg

От
David G Johnston
Дата:
Raj Gandhi wrote
> I'm trying to use format() function with string_agg with Postgres 9.1.9
> but
> getting error - "too few arguments for format"
>
> I have two tables t_message and t_message_args. Table t_message   has
> 'message' column with text in the format 'Test message first arg is %1$s
> and second arg  %2$s end-of-message'
>
>               id             | integer           |
>               message | character varying |
>
>
> And the second table t_message_args contains message argument and values
>               id           | integer           |
>              arg          | integer           |
>              argvalue  | character varying |
>
>
> Here is the SQL that uses format() function to retrieve formatted message
> by replacing arg value in the message:
> =
> select m.id, format(m.message, string_agg(a.argvalue, ',' order by a.arg)
> from t_message m, t_message_args a
> where m.id = a.id
> group by m.id, m.message
>
>
> ERROR:  too few arguments for format
>
> ********** Error **********
>
> ERROR: too few arguments for format
> SQL state: 22023

format('%s %s', '1', '2'); --works
format('%s %s, array['1','2']) -- doesn't work

http://www.postgresql.org/docs/9.3/interactive/xfunc-sql.html

(arguably this is not SQL language specific and should be relocated to a
more generic part of the documentation...)


> You can't actually write that, though — or at least, it will not match
> this function definition. A parameter marked VARIADIC matches one or more
> occurrences of its element type, not of its own type.
>
> Sometimes it is useful to be able to pass an already-constructed array to
> a variadic function; this is particularly handy when one variadic function
> wants to pass on its array parameter to another one. You can do that by
> specifying VARIADIC in the call:

select format('%s %s', VARIADIC array_agg(val)) from (values ('1'),('2'))
src (val) -- works

Note the use of array_agg(); string_agg() returns text, not an array, so its
not going to be of use here - unless you want to build up the specifier
portion on the fly too:

select format(string_agg(fmt_prt, ';'), array_agg(val))
from (values ('%s','1'),('%s','2')) src (fmt_prt, val) --works

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/format-function-with-string-agg-tp5820247p5820251.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: format() function with string_agg

От
Raj Gandhi
Дата:
I tried both SQLs in Postgres 9.1 but they failed with same error - "ERROR: too few arguments for format"

select format('%s %s', VARIADIC array_agg(val)) from (values ('1'),('2'))
src (val) 

select format(string_agg(fmt_prt, ';'), array_agg(val))
from (values ('%s','1'),('%s','2')) src (fmt_prt, val) 

==
ERROR:  too few arguments for format

********** Error **********

ERROR: too few arguments for format
SQL state: 22023


It looks like the error is specific to 9.1 since it worked for you in 9.3.  
I can't upgrade to 9.3 soon. Is there any workaround  or another way to achieve the formatting of message?




On Tue, Sep 23, 2014 at 11:09 PM, David G Johnston <david.g.johnston@gmail.com> wrote:
Raj Gandhi wrote
> I'm trying to use format() function with string_agg with Postgres 9.1.9
> but
> getting error - "too few arguments for format"
>
> I have two tables t_message and t_message_args. Table t_message   has
> 'message' column with text in the format 'Test message first arg is %1$s
> and second arg  %2$s end-of-message'
>
>               id             | integer           |
>               message | character varying |
>
>
> And the second table t_message_args contains message argument and values
>               id           | integer           |
>              arg          | integer           |
>              argvalue  | character varying |
>
>
> Here is the SQL that uses format() function to retrieve formatted message
> by replacing arg value in the message:
> =
> select m.id, format(m.message, string_agg(a.argvalue, ',' order by a.arg)
> from t_message m, t_message_args a
> where m.id = a.id
> group by m.id, m.message
>
>
> ERROR:  too few arguments for format
>
> ********** Error **********
>
> ERROR: too few arguments for format
> SQL state: 22023

format('%s %s', '1', '2'); --works
format('%s %s, array['1','2']) -- doesn't work

http://www.postgresql.org/docs/9.3/interactive/xfunc-sql.html

(arguably this is not SQL language specific and should be relocated to a
more generic part of the documentation...)


> You can't actually write that, though — or at least, it will not match
> this function definition. A parameter marked VARIADIC matches one or more
> occurrences of its element type, not of its own type.
>
> Sometimes it is useful to be able to pass an already-constructed array to
> a variadic function; this is particularly handy when one variadic function
> wants to pass on its array parameter to another one. You can do that by
> specifying VARIADIC in the call:

select format('%s %s', VARIADIC array_agg(val)) from (values ('1'),('2'))
src (val) -- works

Note the use of array_agg(); string_agg() returns text, not an array, so its
not going to be of use here - unless you want to build up the specifier
portion on the fly too:

select format(string_agg(fmt_prt, ';'), array_agg(val))
from (values ('%s','1'),('%s','2')) src (fmt_prt, val) --works

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/format-function-with-string-agg-tp5820247p5820251.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: format() function with string_agg

От
David Johnston
Дата:
Note that the second query you noted has an error - i left out the VARIADIC modifier before the array_agg()

The 9.1 and 9.3 documentation match with respect to the above so it should work in theory...

You should supply the exact queries you attempted; and at the moment I cannot test anything except 9.0 and 9.3 so maybe someone else can confirm.

David J.

On Wed, Sep 24, 2014 at 9:26 AM, Raj Gandhi <raj01gandhi@gmail.com> wrote:
I tried both SQLs in Postgres 9.1 but they failed with same error - "ERROR: too few arguments for format"

select format('%s %s', VARIADIC array_agg(val)) from (values ('1'),('2'))
src (val) 

select format(string_agg(fmt_prt, ';'), array_agg(val))
from (values ('%s','1'),('%s','2')) src (fmt_prt, val) 

==
ERROR:  too few arguments for format

********** Error **********

ERROR: too few arguments for format
SQL state: 22023


It looks like the error is specific to 9.1 since it worked for you in 9.3.  
I can't upgrade to 9.3 soon. Is there any workaround  or another way to achieve the formatting of message?




On Tue, Sep 23, 2014 at 11:09 PM, David G Johnston <david.g.johnston@gmail.com> wrote:
Raj Gandhi wrote
> I'm trying to use format() function with string_agg with Postgres 9.1.9
> but
> getting error - "too few arguments for format"
>
> I have two tables t_message and t_message_args. Table t_message   has
> 'message' column with text in the format 'Test message first arg is %1$s
> and second arg  %2$s end-of-message'
>
>               id             | integer           |
>               message | character varying |
>
>
> And the second table t_message_args contains message argument and values
>               id           | integer           |
>              arg          | integer           |
>              argvalue  | character varying |
>
>
> Here is the SQL that uses format() function to retrieve formatted message
> by replacing arg value in the message:
> =
> select m.id, format(m.message, string_agg(a.argvalue, ',' order by a.arg)
> from t_message m, t_message_args a
> where m.id = a.id
> group by m.id, m.message
>
>
> ERROR:  too few arguments for format
>
> ********** Error **********
>
> ERROR: too few arguments for format
> SQL state: 22023

format('%s %s', '1', '2'); --works
format('%s %s, array['1','2']) -- doesn't work

http://www.postgresql.org/docs/9.3/interactive/xfunc-sql.html

(arguably this is not SQL language specific and should be relocated to a
more generic part of the documentation...)


> You can't actually write that, though — or at least, it will not match
> this function definition. A parameter marked VARIADIC matches one or more
> occurrences of its element type, not of its own type.
>
> Sometimes it is useful to be able to pass an already-constructed array to
> a variadic function; this is particularly handy when one variadic function
> wants to pass on its array parameter to another one. You can do that by
> specifying VARIADIC in the call:

select format('%s %s', VARIADIC array_agg(val)) from (values ('1'),('2'))
src (val) -- works

Note the use of array_agg(); string_agg() returns text, not an array, so its
not going to be of use here - unless you want to build up the specifier
portion on the fly too:

select format(string_agg(fmt_prt, ';'), array_agg(val))
from (values ('%s','1'),('%s','2')) src (fmt_prt, val) --works

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/format-function-with-string-agg-tp5820247p5820251.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


Re: format() function with string_agg

От
Raj Gandhi
Дата:
SQL with VARIADIC also gives the same error in 9.1:

select format('%s %s', VARIADIC array_agg(val)) 
from (values ('1'),('2')) src (val) 
==
ERROR:  too few arguments for format

********** Error **********

ERROR: too few arguments for format
SQL state: 22023


On Wed, Sep 24, 2014 at 9:34 AM, David Johnston <david.g.johnston@gmail.com> wrote:
Note that the second query you noted has an error - i left out the VARIADIC modifier before the array_agg()

The 9.1 and 9.3 documentation match with respect to the above so it should work in theory...

You should supply the exact queries you attempted; and at the moment I cannot test anything except 9.0 and 9.3 so maybe someone else can confirm.

David J.

On Wed, Sep 24, 2014 at 9:26 AM, Raj Gandhi <raj01gandhi@gmail.com> wrote:
I tried both SQLs in Postgres 9.1 but they failed with same error - "ERROR: too few arguments for format"

select format('%s %s', VARIADIC array_agg(val)) from (values ('1'),('2'))
src (val) 

select format(string_agg(fmt_prt, ';'), array_agg(val))
from (values ('%s','1'),('%s','2')) src (fmt_prt, val) 

==
ERROR:  too few arguments for format

********** Error **********

ERROR: too few arguments for format
SQL state: 22023


It looks like the error is specific to 9.1 since it worked for you in 9.3.  
I can't upgrade to 9.3 soon. Is there any workaround  or another way to achieve the formatting of message?




On Tue, Sep 23, 2014 at 11:09 PM, David G Johnston <david.g.johnston@gmail.com> wrote:
Raj Gandhi wrote
> I'm trying to use format() function with string_agg with Postgres 9.1.9
> but
> getting error - "too few arguments for format"
>
> I have two tables t_message and t_message_args. Table t_message   has
> 'message' column with text in the format 'Test message first arg is %1$s
> and second arg  %2$s end-of-message'
>
>               id             | integer           |
>               message | character varying |
>
>
> And the second table t_message_args contains message argument and values
>               id           | integer           |
>              arg          | integer           |
>              argvalue  | character varying |
>
>
> Here is the SQL that uses format() function to retrieve formatted message
> by replacing arg value in the message:
> =
> select m.id, format(m.message, string_agg(a.argvalue, ',' order by a.arg)
> from t_message m, t_message_args a
> where m.id = a.id
> group by m.id, m.message
>
>
> ERROR:  too few arguments for format
>
> ********** Error **********
>
> ERROR: too few arguments for format
> SQL state: 22023

format('%s %s', '1', '2'); --works
format('%s %s, array['1','2']) -- doesn't work

http://www.postgresql.org/docs/9.3/interactive/xfunc-sql.html

(arguably this is not SQL language specific and should be relocated to a
more generic part of the documentation...)


> You can't actually write that, though — or at least, it will not match
> this function definition. A parameter marked VARIADIC matches one or more
> occurrences of its element type, not of its own type.
>
> Sometimes it is useful to be able to pass an already-constructed array to
> a variadic function; this is particularly handy when one variadic function
> wants to pass on its array parameter to another one. You can do that by
> specifying VARIADIC in the call:

select format('%s %s', VARIADIC array_agg(val)) from (values ('1'),('2'))
src (val) -- works

Note the use of array_agg(); string_agg() returns text, not an array, so its
not going to be of use here - unless you want to build up the specifier
portion on the fly too:

select format(string_agg(fmt_prt, ';'), array_agg(val))
from (values ('%s','1'),('%s','2')) src (fmt_prt, val) --works

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/format-function-with-string-agg-tp5820247p5820251.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



Re: format() function with string_agg

От
Tom Lane
Дата:
Raj Gandhi <raj01gandhi@gmail.com> writes:
> SQL with VARIADIC also gives the same error in 9.1:
> select format('%s %s', VARIADIC array_agg(val))
> from (values ('1'),('2')) src (val)
> ==
> ERROR:  too few arguments for format

You need 9.3 or later for that to work.  Per the 9.3 release notes:

    * Allow concat() and format() to properly expand VARIADIC-labeled arguments (Pavel Stehule)

            regards, tom lane


Re: format() function with string_agg

От
David Johnston
Дата:
On Wednesday, September 24, 2014, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Raj Gandhi <raj01gandhi@gmail.com> writes:
> SQL with VARIADIC also gives the same error in 9.1:
> select format('%s %s', VARIADIC array_agg(val))
> from (values ('1'),('2')) src (val)
> ==
> ERROR:  too few arguments for format

You need 9.3 or later for that to work.  Per the 9.3 release notes:

    * Allow concat() and format() to properly expand VARIADIC-labeled arguments (Pavel Stehule)

                        regards, tom lane

Found it.


In the comments after table 9.6

This whole feature could maybe be made more visible in the 9.3 documentation but (arguably) since it was a feature and not a bug fix back patching was not possible.  Specifically it seems like it was too invasive a change to make it work properly in the back branches.

David J.

Re: format() function with string_agg

От
Raj Gandhi
Дата:
Thanks David and Tom.  Unfortunately I can't immediately upgrade to 9.3.


On Wed, Sep 24, 2014 at 11:57 AM, David Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, September 24, 2014, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Raj Gandhi <raj01gandhi@gmail.com> writes:
> SQL with VARIADIC also gives the same error in 9.1:
> select format('%s %s', VARIADIC array_agg(val))
> from (values ('1'),('2')) src (val)
> ==
> ERROR:  too few arguments for format

You need 9.3 or later for that to work.  Per the 9.3 release notes:

    * Allow concat() and format() to properly expand VARIADIC-labeled arguments (Pavel Stehule)

                        regards, tom lane

Found it.


In the comments after table 9.6

This whole feature could maybe be made more visible in the 9.3 documentation but (arguably) since it was a feature and not a bug fix back patching was not possible.  Specifically it seems like it was too invasive a change to make it work properly in the back branches.

David J.