pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConcatenating multiple fetches into a single string
4+ messages / 2 participants
[nested] [flat]
* Concatenating multiple fetches into a single string
@ 2003-12-01 12:01 Kumar <sgnerd@yahoo.com.sg>
2003-12-01 20:01 ` Re: Concatenating multiple fetches into a single string Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 4+ messages in thread
From: Kumar @ 2003-12-01 12:01 UTC (permalink / raw)
To: pgsql-sql
Dear Friends,
I am doing a migration from SQL server to Postgres SQL. A simple select fetches the following names.
select full_name FROM project_members where project_members.project_role_id in (' + @p_res_ids + ') ;
Let us say if the results are
full_name
---------------
David
Postgres
plpgsql
I need to send the out put as David,Postgres,Plsql -- one string, concatenates the fetches from multiple rows. This was handled in SQL server with a cursor.
Can this be handled only by sql manipulation or need to use cursors. If i use cursor in postgres, what is the equivalent of MS SQL Statement
WHILE @@FETCH_STATUS = 0.
please shed some light pls.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Concatenating multiple fetches into a single string
2003-12-01 12:01 Concatenating multiple fetches into a single string Kumar <sgnerd@yahoo.com.sg>
@ 2003-12-01 20:01 ` Tomasz Myrta <jasiek@klaster.net>
2003-12-02 04:51 ` Re: Concatenating multiple fetches into a single string Kumar <sgnerd@yahoo.com.sg>
0 siblings, 1 reply; 4+ messages in thread
From: Tomasz Myrta @ 2003-12-01 20:01 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
Dnia 2003-12-01 13:01, Użytkownik Kumar napisał:
> Dear Friends,
>
> I am doing a migration from SQL server to Postgres SQL. A simple select
> fetches the following names.
>
> select full_name FROM project_members where
> project_members.project_role_id in (' + @p_res_ids + ') ;
>
> Let us say if the results are
>
> full_name
> ---------------
> David
> Postgres
> plpgsql
>
> I need to send the out put as David,Postgres,Plsql -- one string,
> concatenates the fetches from multiple rows. This was handled in SQL
> server with a cursor.
I can't find simpler function (if I remember well, there is such one),
so there is my version of aggregate function you need:
create or replace function comma_aggregate(varchar,varchar) returns
varchar as '
begin
if length($1)>0 and length($2)>0 then
return $1 || '', '' || $2;
elsif length($2)>0 then
return $2;
end if;
return $1;
end;
' language 'plpgsql';
drop aggregate comma(varchar) cascade;
create aggregate comma (basetype=varchar, sfunc=comma_aggregate,
stype=varchar, initcond='' );
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Concatenating multiple fetches into a single string
2003-12-01 12:01 Concatenating multiple fetches into a single string Kumar <sgnerd@yahoo.com.sg>
2003-12-01 20:01 ` Re: Concatenating multiple fetches into a single string Tomasz Myrta <jasiek@klaster.net>
@ 2003-12-02 04:51 ` Kumar <sgnerd@yahoo.com.sg>
2003-12-02 06:07 ` Re: Concatenating multiple fetches into a single string Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 4+ messages in thread
From: Kumar @ 2003-12-02 04:51 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Thanks for your reply.
But how to use this comma_aggregate( ) function to concatenate the fetched
columns values from a select statement. In my example my select stmt fetches
the following 3 rows. How can I use this function to concatenate them.
Select full_name FROM project_members where project_members.project_role_id
in ( ' x,y,z ') ;
full_name
---------------
David
Postgres
plpgsql
Expected return string is - 'David,Postgres,Plsql'
Regards
Kumar
----- Original Message -----
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Kumar" <sgnerd@yahoo.com.sg>
Cc: "psql" <pgsql-sql@postgresql.org>
Sent: Tuesday, December 02, 2003 1:31 AM
Subject: Re: [SQL] Concatenating multiple fetches into a single string
> Dnia 2003-12-01 13:01, Użytkownik Kumar napisał:
> > Dear Friends,
> >
> > I am doing a migration from SQL server to Postgres SQL. A simple select
> > fetches the following names.
> >
> > select full_name FROM project_members where
> > project_members.project_role_id in (' + @p_res_ids + ') ;
> >
> > Let us say if the results are
> >
> > full_name
> > ---------------
> > David
> > Postgres
> > plpgsql
> >
> > I need to send the out put as David,Postgres,Plsql -- one string,
> > concatenates the fetches from multiple rows. This was handled in SQL
> > server with a cursor.
>
> I can't find simpler function (if I remember well, there is such one),
> so there is my version of aggregate function you need:
>
>
> create or replace function comma_aggregate(varchar,varchar) returns
> varchar as '
> begin
> if length($1)>0 and length($2)>0 then
> return $1 || '', '' || $2;
> elsif length($2)>0 then
> return $2;
> end if;
> return $1;
> end;
> ' language 'plpgsql';
>
> drop aggregate comma(varchar) cascade;
> create aggregate comma (basetype=varchar, sfunc=comma_aggregate,
> stype=varchar, initcond='' );
>
> Regards,
> Tomasz Myrta
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Concatenating multiple fetches into a single string
2003-12-01 12:01 Concatenating multiple fetches into a single string Kumar <sgnerd@yahoo.com.sg>
2003-12-01 20:01 ` Re: Concatenating multiple fetches into a single string Tomasz Myrta <jasiek@klaster.net>
2003-12-02 04:51 ` Re: Concatenating multiple fetches into a single string Kumar <sgnerd@yahoo.com.sg>
@ 2003-12-02 06:07 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-12-02 06:07 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
Dnia 2003-12-02 05:51, Użytkownik Kumar napisał:
> Thanks for your reply.
>
> But how to use this comma_aggregate( ) function to concatenate the fetched
> columns values from a select statement. In my example my select stmt fetches
> the following 3 rows. How can I use this function to concatenate them.
>
> Select full_name FROM project_members where project_members.project_role_id
> in ( ' x,y,z ') ;
>
> full_name
> ---------------
> David
> Postgres
> plpgsql
>
> Expected return string is - 'David,Postgres,Plsql'
Where is the problem?
It's an aggregate function - you use it like the other ones:
select comma(full_name) from...
There is one problem with this function - strings order is unexpectable,
but you can always sort them in subselect before using this function:
select comma(full_name) from
(select full_name from .... order by full_name) X;
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-12-02 06:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-01 12:01 Concatenating multiple fetches into a single string Kumar <sgnerd@yahoo.com.sg>
2003-12-01 20:01 ` Tomasz Myrta <jasiek@klaster.net>
2003-12-02 04:51 ` Kumar <sgnerd@yahoo.com.sg>
2003-12-02 06:07 ` Tomasz Myrta <jasiek@klaster.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox