Looping through string constants

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/