[SQL] Concatenating multiple fetches into a single string
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.
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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Permissions problem on 7.4
Hi
I hope this is the correct list to ask about a permissions problem I have.
I create a schema named cust, a table named clients, and a group named
salesmen.
I use:
REVOKE ALL ON SCHEMA cust FROM PUBLIC;
GRANT USAGE ON SCHEMA cust TO GROUP salesmen;
REVOKE ALL ON TABLE clients FROM PUBLIC;
GRANT SELECT ON TABLE clients TO GROUP salesmen;
then I do a :
\dp clients
and get:
Access privileges for database "custdb"
Schema | Table |Access
privileges
-+-+-
cust| clients | {admin=a*r*w*d*R*x*t*/cust,"group salesmen=r/cust"}
The problem is that when I check from within my application if a member
of the group salesmen has permissions to INSERT into the group clients,
I get an affirmative question.
I guessed it was a problem with my application, so I downloaded
postgresql_autodoc (http://www.rbt.ca/autodoc/) in order to check the
permissions better.
It also claims that group salesmen has the right to SELECT, INSERT and
DELETE on table clients.
I tried :
revoke insert on clients from group salesmen;
and nothing changed
I tried :
revoke all on clients from group salesmen;
and every permission was revoked.
Then I tried again:
GRANT SELECT ON TABLE clients TO GROUP salesmen;
and all three permissions (insert, select, delete) are back in place.
All these happen with postgresql 7.4 on linux
Is there something I'm doing wrong, something I don't understand or have
I hit a bug?
Thanks in advance
Michalis Kabrianis
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Permissions problem on 7.4
Michalis Kabrianis writes:
> Schema | Table |Access
> privileges
> -+-+-
> cust| clients | {admin=a*r*w*d*R*x*t*/cust,"group salesmen=r/cust"}
>
>
> The problem is that when I check from within my application if a member
> of the group salesmen has permissions to INSERT into the group clients,
> I get an affirmative question.
Fix your application.
> I guessed it was a problem with my application, so I downloaded
> postgresql_autodoc (http://www.rbt.ca/autodoc/) in order to check the
> permissions better.
> It also claims that group salesmen has the right to SELECT, INSERT and
> DELETE on table clients.
Report a bug to the authors of that program.
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] XML & Postgres Functions
Hi all- I'm migrating to postgres from the MS SQL Server land, as I can see a great potential with postgres, I was wondering if anyone has experimented or started a project with XML inside user defined functions? I've seen the contrib/xml shipped with the distro, as I see it's usefulness, it's not quite what I had in mind (lacks the ablity to return recordset/table of nodes). Also, the project XpSQL seems interesting, but still not it. I'm looking for a solution similar to the OpenXML that MS implements in their SQL Server. A way to parse XML into a table and/or a temp table for use inside a function. For example, I would like to serialize my objects into XML, send them to a postgres function for saving/updating the relational data inside postgres. I envision something of the following could be useful: XML -- ... UDF -- create function somefunc(xmldata text) begin xmldoc := preparedoc(xmldata); ... update tablename set field1=x.field1, field2=x.field2, from xmltable(xmldoc, '/table/[EMAIL PROTECTED]') as x ... preform removedoc(xmldoc); end; By using XML & XPath, one could be very creative preforming updates to the tables via the xml data passed to the function. By having this functionality, one could have their applications communicate with XML structures instead of database structures and either side could change without drastically effecting the other. Also, by have the application obey XML structures, the database schema is unknown and therefore essentially hidden from the programmers as they don't need to be bothered with how the data is actually stored. While my motivation isn't to hide the database schema from programmers, but to provide simple method for having applications send data to the data tier for storage. It's much easier to define complex object structure in XML and then let the database worry about storing it relationally. I'm not sure if I'm missing the point with postgres, but we've been doing this with MS SQL Server for sometime, while it's not the most effienct method of sending data to the database, I find it most elegant from the application side, simplify code and promotes code reusablity. Has anyone done this before with postgres, if so could you share your experiences? Also, if no one has attempted this, does anyone else share the need for something like this? I'm thinking about starting this project if there is a greater need out there. Best Regards, David [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Concatenating multiple fetches into a single string
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" <[EMAIL PROTECTED]>
To: "Kumar" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Problem with intervals
I'm getting an unexpected result using intervals in an expression:
select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
date
2003-10-26
(1 row)
When I get rid of the date cast it becomes clear what is happening:
select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;
?column?
2003-10-26 23:00:00-08
(1 row)
I assumed '1 day' would always increment the date by 1, but it appears
that '1 day' just means '24 hours', and due to the daylight/standard
time shift, October 26 was 25 hours long this year.
Is this a Postgres bug, or is this correct SQL behavior? I'm running
Postgres 7.2.2.
Bob S.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Concatenating multiple fetches into a single string
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
