Re: [SQL] Counting the rows INSERTed/UPDATEd?
Mario Splivalo wrote: I have found, I thinl, in the pg manual, the way to get the number of rows inserted/updated, from within the plpgsql. I can't find it anymore, is that still there, or I misread something earlier? http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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] Counting the rows INSERTed/UPDATEd?
On Tue, 2006-05-02 at 09:13 +0100, Richard Huxton wrote: > Mario Splivalo wrote: > > I have found, I thinl, in the pg manual, the way to get the number of > > rows inserted/updated, from within the plpgsql. I can't find it anymore, > > is that still there, or I misread something earlier? > > http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS > Yes, that is it. Thank yall, I guess I was a bit tired and a bit lazy. Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Creating nested functions with plpgsql
Hi! Is it possible to create nested functions using plpgsql as the language? I'd like to avoid other dependencies if I can, besides using already written SQL code... There are lots of repetitive tasks that "subfunctions" would solve in a very elegant way, keeping code more readable and concise. I was thinking something along the lines of: CREATE FUNCTION outer_function(param1 DATE) RETURNS date AS $$ DECLARE BEGIN CREATE FUNCTION inner_function(OUT output_day DATE) AS $_$ DECLARE output_day DATE; BEGIN -- do something to calculate output_day END; $_$ language plpgsql stable strict; -- here I could have other languages or -- even restrict the inner function to the -- same language as the outer function... -- $_$ is different from $$ intentionally. -- do something in main function that uses inner function several -- times. END; $$ language plpgsql; I have some real case examples where this could be useful, if it is needed. I haven't pasted them here because the smallest one has 176 LOC, after refactoring with nested functions. If it is not possible, are there any plans to allow this kind of thing? (Even with a different syntax it would be good to have it.) TIA, -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] ERROR: plan should not reference subplan's variable
Hi * I'm trying to run the following SQL statement on a PostgreSQL 8.1, installed on a Windows machine: INSERT INTO PROJECT(PROJECT_ID,PROJECT_DESC) (SELECT MAX(PROJECT_ID),'MYPROJECT'FROM PROJECT WHERE NOT EXISTS ( SELECT PROJECT_DESC FROM PROJECT WHERE PROJECT_DESC = 'MYPROJECT' )) and I get the following error: ERROR: plan should not reference subplan's variable If, for example, I replace MAX with some other aggregation (e.g. AVG or SUM), everything works ok. The table DDL looks like: CREATE TABLE project( project_id int4 NOT NULL, project_desc varchar(255), CONSTRAINT project_pkey PRIMARY KEY (project_id)) WITH OIDS; Do you have any clue why does this happen? Thanks, Catalin
Re: [SQL] ERROR: plan should not reference subplan's variable
"Catalin Pitis" <[EMAIL PROTECTED]> writes: > ERROR: plan should not reference subplan's variable > Do you have any clue why does this happen? It's a bug :-(. Thanks for the test case --- I'll look into it tonight or tomorrow, if no one beats me to it. regards, tom lane ---(end of broadcast)--- TIP 1: 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] Sorting aggregate column contents
Is it possible to sort the content of an aggregate text column? Query: select s.name, ag_concat(c.name) from state s inner join city c on (c.idstate = s.idstate) group by s.name order by s.name; Result: name | ag_concat ---+--- RS| Porto Alegre, Gramado SP| Osasco (2 rows) Expected result: name | ag_concat ---+--- RS| Gramado, Porto Alegre SP| Osasco (2 rows) I tried "order by s.name, c.name" but it causes a error: ERROR: column "c.name" must appear in the GROUP BY clause or be used in an aggregate My function and aggregate code: CREATE FUNCTION f_concat (text, text) RETURNS text AS $$ DECLARE t text; BEGIN IF character_length($1) > 0 THEN t = $1 || ', ' || $2; ELSE t = $2; END IF; RETURN t; END; $$ LANGUAGE plpgsql; CREATE AGGREGATE ag_concat ( sfunc = f_concat, basetype = text, stype = text, initcond = '' ); -- Everton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Sorting aggregate column contents
On May 02 06:00, Everton Luís Berz wrote: > Is it possible to sort the content of an aggregate text column? > > Query: > select s.name, ag_concat(c.name) from state s > inner join city c on (c.idstate = s.idstate) > group by s.name > order by s.name; IMHO, you can receive results ordered by using a subselect: SELECT T.s_name, ag_concat(T.c_name) FROM (SELECT s.name, c.name FROM state AS s INNER JOIN city AS c ON (c.idstate = s.idstate) ORDER BY s.name, c.name) AS T (s_name, c_name) GROUP BY T.s_name; Regards. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Sorting aggregate column contents
On Wed, May 03, 2006 at 00:13:40 +0300, Volkan YAZICI <[EMAIL PROTECTED]> wrote: > On May 02 06:00, Everton Luís Berz wrote: > > Is it possible to sort the content of an aggregate text column? > > > > Query: > > select s.name, ag_concat(c.name) from state s > > inner join city c on (c.idstate = s.idstate) > > group by s.name > > order by s.name; > > IMHO, you can receive results ordered by using a subselect: > > SELECT T.s_name, ag_concat(T.c_name) > FROM (SELECT s.name, c.name > FROM state AS s > INNER JOIN city AS c ON (c.idstate = s.idstate) > ORDER BY s.name, c.name) AS T (s_name, c_name) > GROUP BY T.s_name; Note that this is nonstandard, but is an intentional (but I am not sure if it's documented) feature of Postgres. There can be some similar situations where you need to use OFFSET 0 to prevent optimizations that will break the ordering. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Sorting aggregate column contents
It works fine. But I wouldn't like using subselect's, then if somebody else knows about an operator or something like that to put on the aggregator, please tell me. Volkan YAZICI escreveu: On May 02 06:00, Everton Luís Berz wrote: Is it possible to sort the content of an aggregate text column? Query: select s.name, ag_concat(c.name) from state s inner join city c on (c.idstate = s.idstate) group by s.name order by s.name; IMHO, you can receive results ordered by using a subselect: SELECT T.s_name, ag_concat(T.c_name) FROM (SELECT s.name, c.name FROM state AS s INNER JOIN city AS c ON (c.idstate = s.idstate) ORDER BY s.name, c.name) AS T (s_name, c_name) GROUP BY T.s_name; Regards. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] ERROR: plan should not reference subplan's variable
"Catalin Pitis" <[EMAIL PROTECTED]> writes: > ERROR: plan should not reference subplan's variable I've applied a patch for this; will be in 8.1.4. http://archives.postgresql.org/pgsql-committers/2006-05/msg00016.php regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] grant select on database demo to user
I find myself with long lists of tables grant select on xx_tax to user; grant select on xx_trip to user; grant select on xx_foo to user; Is there a way to grant to all tables, with a single grant? I know how to do it in mysql, but not postgres. As close as I get it: #grant select on database demo to user ERROR: invalid privilege type SELECT for database -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] grant select on database demo to user
Hi,You could easily generate a script with the list of tables and the required grant information and then run it to give the necessary grants. (The list of tables may be obtained by querying tables in 'information_schema')Regards,-Thusitha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bryce Nesbitt Sent: Wednesday, May 03, 2006 9:03 AM To: pgsql-sql@postgresql.org Subject: [SQL] grant select on database demo to user I find myself with long lists of tables grant select on xx_tax to user; grant select on xx_trip to user; grant select on xx_foo to user; Is there a way to grant to all tables, with a single grant? I know how to do it in mysql, but not postgres. As close as I get it: #grant select on database demo to user ERROR: invalid privilege type SELECT for database -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match --
Re: [SQL] Sorting aggregate column contents
It works fine. But I wouldn't like using subselect's, then if somebody else knows about an operator or something like that to put on the aggregator, please tell me. I think the nature of the f_concat makes it difficult to sort, since it simply adds the next value, so if the source table gives value in the order of 'a','c','d','b' there's no way to handle them within f_concat unless you modify and rearrange the previous result string from within f_concat. So the source table (city) should be sorted. I don't know if this is a standard way, but this one seems to do that. == select s.name, ag_concat(c.name) from state s inner join (select * from city order by name desc) as c on c.idstate=s.idstate group by s.name order by 1; OR select s.name, ag_concat(c.name) from state s, (select * from city order by name desc) as c where c.idstate = s.idstate group by s.name order by 1; == I'm just reordering the source table on the fly. Curiously, if you don't have 'desc' you'll get a reverse ordered list. (z,...,a) I think your needs may also be met without any aggregator as well (there may be marginal cases which I haven't thought of, but I assume they can be handled if needed) == select s.name, array_to_string(array(select name from city where idstate = s.idstate order by name),',') from state s; == name | array_to_string --+- RP | Gramado,Port Alegre SP | Osasco * I see normalization issue here but guess it's not important. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] grant select on database demo to user
am 02.05.2006, um 20:32:54 -0700 mailte Bryce Nesbitt folgendes: > I find myself with long lists of tables > > grant select on xx_tax to user; > grant select on xx_trip to user; > grant select on xx_foo to user; > > Is there a way to grant to all tables, with a single grant? I know how No, but you can use a little Script, please read: http://people.planetpostgresql.org/greg/index.php?/archives/38-guid.html#extended HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] ERROR: plan should not reference subplan's variable
Hi Tom Could you tell me when will 8.1.4 be released with the problem solved? Thanks, Catalin On 5/3/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Catalin Pitis" <[EMAIL PROTECTED]> writes: > ERROR: plan should not reference subplan's variableI've applied a patch for this; will be in 8.1.4.http://archives.postgresql.org/pgsql-committers/2006-05/msg00016.php regards, tom lane
Re: [SQL] LinkedList
The problem is that your way, there is no indicated way to determine which node is which. For instance is you update any of your nodes then the node list would be out of order and your list would not work. I think the thinking is different here. The OP's list is ordered and has prev-next only, and there can be lists that are read only and/or ordered (like clickstream or a data stream out of multi-stream packets) and do not require insert. That's why I mentioned it's for traverse-only in my original post. (But I disagree with you about not being able to determine a node - since in sql it's possible to identify a row as long as it has unique values in fields, however they are named) After I posted the message I realized there is another way to do this without adding an extra field, and it would be a closer example to how it is done in C. If you assigned the OID of the previous and next nodes rather than arbitrary integer, you could access each node independent of the order they are listed. I have not messed around much with OIDs. I am not sure if OIDs change if an entry is updated. I understand oid doesn't change with update. But tables may or may not have oids. (can be created "without oids") I also came to appreciate the difference with C. In sql, there is a way to identify a row like I did, but in C it'd not be possible without the address (of course it's not like "impossible" but ...), so the linked list as in strict C-like sense would be perfect but may carry a different value here. (Since we already have the added layer of sql engines.) I agree your method would be better if we want to scale when insert or delete is needed. It'd be interesting to compare how the normal O() applies to sql - would updating n rows with one sql statement be equivalent to O(n) in C? Maybe a silly question but it came to my mind... In C you would use a pointer to storage location of previous and next "node" which is similar to using the OID. In some cases it can be necessary to use pointers to pointers when accessing variable length relocatable data, but that goes way past what this thread is about. The example I provided, is still feasible and alleviates all unknowns at the expense of 4 bytes of storage for one integer used as a fixed address for each node. As long as it works in real world use. Without some way of addressing each node, the idea of a linked list seems wrong, since a linked is supposed to hold the address of the previous and or next item in the list, assuming the data is always going to be correctly sorted so that you can locate the next item by tupple number seems overly assumptive. If it works for you great, your example may then be useful as a short cut, but I don't believe in leaving things to chance when programming. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] i am getting error when i am using copy command
Hi .. 1) I created one table 2) Create table penchal(id integer, name varchar(12),age integer); 3) Then I inserted some values into this table……… 4) Insert into penchal values(1,’reddy’,2); 5) Select * from penchal ; I used this statement to display the values in a table 6) Finaly I want to use copy …. 7) But I am getting error when I am using this copy statement Copy penchal to ‘/tmp/penchal.out’ When I am using this statement I am getting error is : could not open file ‘/tmp/penchal.out’ for writing: no such file or directory.. May I know y I am getting this error…….. pls any one can help me for this.. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-4133-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] join on a like
A friend suggested that I use the Like predicate as part of my join condition and I was wondering if that was wise. For example, I have a column that describes the level of an event. There is A,B,C and then they can have children, such as AA,AB,BA,BB and they can all have children as well. So if I wanted to see all the children with their parents at every level it would be: select a.* from tbl1 a join tbl1 b on a.level like b.level || '%' That would give me on one side A and on the other side A,AA,AB,AAAB,... as well as AA and on the other side all of its children. I'm just nervous about using a Like in a join. ---(end of broadcast)--- TIP 1: 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] [ocpfree] For my direct Client Looking for Mainframe const urgently
I am having a mainframe requirement. Following are the details contact me with resume , rate , contact details to : [EMAIL PROTECTED] SKILLSET : Cobol / CICS / DB2 / JCL LOCATION : Hartford, CT CLIENT : Leading Insurance company in Hartford Sachin P Kraftware Inc kraftware.com [EMAIL PROTECTED] 4024083687 4024086817 Note: We respect your Online Privacy. This is not an unsolicited mail. Under Bills.1618 Title III passed by the 105th U.S. Congress this mail cannot be considered Spam as long as we include Contact information and a method to be removed from our mailing list. If you are not interested in receiving our e-mails then please reply with a "REMOVE" in the subject line at [EMAIL PROTECTED] and mention all the e-mail addresses to be removed with any e-mail addresses, which might be diverting the e-mails to you. We are sorry for the inconv Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ocpfree/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] join on a like
I guess the real question is: what is more efficient - a join on a LIKE or a PLPGSQL function that has 2 loops, 1 for each bottom level child and one that takes the substring of that child one character at a time to get each parent? It sounds like you are saying that the join will actaully use the index and therefore it is optimal, not just possible. [EMAIL PROTECTED] wrote: If you use a btree index on the column, you should be able to use it in the like: http://www.postgresql.org/docs/8.1/interactive/indexes-types.html. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Compute hash of a table?
Hi All I need to determine whether the content of a given table has changed from the previous time I've checked it. so, my initial idea is to calculate a hash value of the content of the whole table, may be with custom aggregate function or something. My question is: Is it possible to get the last time(stamp) when the content of a given table in pgsql has changed??? Thanks! Pete ---(end of broadcast)--- TIP 6: explain analyze is your friend
