Hello,

I have a table similar to the following:

id probe value
1 asdf 10
1 qwer 20
1 zxcv 30
2 asdf 40
2 qwer 50
2 zxcv 60

I would like to create a stored procedure or function that will make a view
with the data as follows:

1 2
asdf 10 40
qwer 20 50
zxcv 30 60

Does anyone know how to do this? I am attempting to make a stored procedure
that...
1. SELECT all distinct "id"s
2. FOR LOOP to iterate over each "id", SELECT probe, value WHERE
id=<current id>
3. JOIN resulting table to table from previous iteration of the FOR loop

Unfortunately, I am not sure if this is a good way to do this. And I am not
familiar with stored procedure syntax. Can anyone help with this?

Thanks,
RC


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Use-for-loop-in-stored-procedure-to-join-query-results-tp3286416p3286416.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Search Discussions

  • Andreas Kretschmer at Nov 30, 2010 at 5:50 pm

    rchowdhury wrote:


    Hello,

    I have a table similar to the following:

    id probe value
    1 asdf 10
    1 qwer 20
    1 zxcv 30
    2 asdf 40
    2 qwer 50
    2 zxcv 60

    I would like to create a stored procedure or function that will make a view
    with the data as follows:

    1 2
    asdf 10 40
    qwer 20 50
    zxcv 30 60

    Does anyone know how to do this? I am attempting to make a stored procedure
    test=*# select * from foo;
    id | probe | value
    ----+-------+-------
    1 | asdf | 10
    1 | qwer | 20
    1 | zxcv | 30
    2 | asdf | 40
    2 | qwer | 50
    2 | zxcv | 60
    (6 Zeilen)

    Zeit: 0,275 ms
    test=*# select probe,
    sum(case when id=1 then value else null end) as "1",
    sum(case when id=2 then value else null end) as "2"
    from foo
    group by 1
    order by 1;
    probe | 1 | 2
    -------+----+----
    asdf | 10 | 40
    qwer | 20 | 50
    zxcv | 30 | 60
    (3 Zeilen)



    Andreas
    --
    Really, I'm not out to destroy Microsoft. That will just be a completely
    unintentional side effect. (Linus Torvalds)
    "If I was god, I would recompile penguin with --enable-fly." (unknown)
    Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
  • Rchowdhury at Nov 30, 2010 at 7:26 pm
    That worked perfectly. Thanks Andreas!
    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/Use-for-loop-in-stored-procedure-to-join-query-results-tp3286416p3286600.html
    Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedNov 30, '10 at 5:35p
activeNov 30, '10 at 7:26p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Rchowdhury: 2 posts Andreas Kretschmer: 1 post

People

Translate

site design / logo © 2023 Grokbase