Обсуждение: format() function with string_agg
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 |
=
select m.id, format(m.message, string_agg(a.argvalue, ',' order by a.arg)
from t_message m, t_message_args a
group by m.id, m.message
ERROR: too few arguments for format
********** Error **********
ERROR: too few arguments for format
SQL state: 22023
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.
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)
src (val)
select format(string_agg(fmt_prt, ';'), array_agg(val))
from (values ('%s','1'),('%s','2')) src (fmt_prt, 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 wroteformat('%s %s', '1', '2'); --works> 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, 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
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 formatSQL state: 22023It 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 wroteformat('%s %s', '1', '2'); --works> 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, 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
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 formatSQL state: 22023It 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 wroteformat('%s %s', '1', '2'); --works> 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, 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
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
On Wednesday, September 24, 2014, Tom Lane <tgl@sss.pgh.pa.us> wrote:
In the comments after table 9.6
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.
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.
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 laneFound it.In the comments after table 9.6This 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.