Thread: Looping through string constants
I'd like to loop through a group of constant string values using plpgsql The best analog i can think of would be in a shell script #!/usr/bin/ksh for a in a b c d e; do echo $a done ./a.ksh a b c d e Is there some tricky way I can make that happen in postgres? (I don't want to put the values in a table =) that would be too easy!) Thanks Dave
David Kerr <dmk@mr-paradox.net> writes:
> I'd like to loop through a group of constant string values using plpgsql
> The best analog i can think of would be in a shell script
> #!/usr/bin/ksh
> for a in a b c d e; do
Use VALUES?
regression=# create function foo() returns int as $$
regression$# declare s int := 0;
regression$# r record;
regression$# begin
regression$# for r in values (1),(2),(3),(4) loop
regression$# s := s + r.column1;
regression$# end loop;
regression$# return s;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select foo();
foo
-----
10
(1 row)
regards, tom lane
David Kerr <dmk@mr-paradox.net> wrote:
> I'd like to loop through a group of constant string values using plpgsql
> The best analog i can think of would be in a shell script
> #!/usr/bin/ksh
> for a in a b c d e; do
> echo $a
> done
> ./a.ksh
> a
> b
> c
> d
> e
> Is there some tricky way I can make that happen in postgres?
> (I don't want to put the values in a table =) that would be too easy!)
If you do not want to use arrays, you can always use:
| FOR r IN SELECT a FROM (VALUES ('a'), ('b'), ('c'), ('d'), ('e')) AS t(a) ORDER BY a LOOP
| RAISE NOTICE '%', r.a;
| END LOOP;
Tim
On Wed, Aug 12, 2009 at 07:10:16PM -0400, Tom Lane wrote: - David Kerr <dmk@mr-paradox.net> writes: - > I'd like to loop through a group of constant string values using plpgsql - > The best analog i can think of would be in a shell script - > #!/usr/bin/ksh - - > for a in a b c d e; do - - Use VALUES? looks like that'll do it, thanks! Dave
>> Using arrays makes it a little less verbose and easier to manage IMO.
>>
>> SELECT v FROM unnest(array['a','b','c','d']) v
>>
>
> Is that 8.4? or is unnest from contrib/ ?
>
> thanks!
>
> Dave
Unnest is included in 8.4, but it's pretty much essential for working
with arrays. Pre 8.4, you'd add the function like so
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
generate_series(array_lower($1,1),
array_upper($1,1)) i;
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote: > CREATE OR REPLACE FUNCTION unnest(anyarray) > RETURNS SETOF anyelement AS > $BODY$ > SELECT $1[i] FROM > generate_series(array_lower($1,1), > array_upper($1,1)) i; > $BODY$ > LANGUAGE 'sql' IMMUTABLE STRICT I'd recommend taking off the "STRICT" from this. It will, counter intuitively, slow things down when you're not expecting it. -- Sam http://samason.me.uk/
> On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote: >> CREATE OR REPLACE FUNCTION unnest(anyarray) >> RETURNS SETOF anyelement AS >> $BODY$ >> SELECT $1[i] FROM >> generate_series(array_lower($1,1), >> array_upper($1,1)) i; >> $BODY$ >> LANGUAGE 'sql' IMMUTABLE STRICT > > I'd recommend taking off the "STRICT" from this. It will, counter > intuitively, slow things down when you're not expecting it. > Woah! Really? I use strict a lot when it doesn't make sense to process a function with a null param. Can you give me more details or point me to more reading on this issue? Thanks.
On Thu, Aug 13, 2009 at 08:30:07AM -0700, Scott Bailey wrote: > >On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote: > >>CREATE OR REPLACE FUNCTION unnest(anyarray) > >> RETURNS SETOF anyelement AS > >>$BODY$ > >>SELECT $1[i] FROM > >> generate_series(array_lower($1,1), > >> array_upper($1,1)) i; > >>$BODY$ > >> LANGUAGE 'sql' IMMUTABLE STRICT > > > >I'd recommend taking off the "STRICT" from this. It will, counter > >intuitively, slow things down when you're not expecting it. > > Woah! Really? I use strict a lot when it doesn't make sense to process a > function with a null param. Can you give me more details or point me to > more reading on this issue? Thanks. There have been a few to-and-fros between me and other people about this. It's basically awkward interaction with the optimizer not being able to expand this out because it may change behavior. Try: http://archives.postgresql.org/pgsql-general/2009-06/msg00233.php IMMUTABLE is good though, don't go removing that yet! -- Sam http://samason.me.uk/