[SQL] What is PG best practice for storing temporary data in functions?
Title: Message There is a very common technique used in other RDBMS (e.g. Sybase) stored procedure programming which I am not sure how best to replicate in Postgres functions. A Sybase stored procedure can do select into #temp1 create table #temp2 (…) call some proc which also uses #temp1 and #temp2 etc where #temp1 and #temp2 are temporary tables magically created by the server for the duration of the procedure call only (the procedure can be safely executed in parallel since each execution sees only its own data). Under the hood, it does this by mangling the names of the temp tables with a unique identifier for the procedure context. What are the cleanest and most performant ways to do this in Postgres (7.4.2)? I am aware of temporary tables but they are globally visible to other invocations in the same session. We need the equivalent of local method variables I guess.
[SQL] Tree structure
Title: Tree structure Anybody know how to make a tree structure using related tables using Postgres. Something like a directory structure is what I'm aiming for. I'm sure there is an easy way but I'm having probs. Any help would be appreciated. Ben.
RE: [SQL] Tree structure
Title: RE: [SQL] Tree structure Any details you can supply would be appreciated. I have got a table at the moment as: id | group_name | parentid --- 1 | Main | 2 | Group | 3 | User | 4 | Group1 | 1 5 | Group2 | 1 6 | Group2-1 | 5 Just for testing. I can see how it should work but the client side code is defeating me at the moment. I'm working in delphi with winzeos (cracking components by the way). Anyone done this. Thanks for any help Ben Trewern > -Original Message- > From: kovacsz [mailto:[EMAIL PROTECTED]] > Sent: 12 September 2000 10:23 > To: Zeljko Trogrlic > Cc: Trewern, Ben; '[EMAIL PROTECTED]' > Subject: Re: [SQL] Tree structure > > > We are using tree structures (and partially ordered trees) with > PostgreSQL. After the beginning success we got into speed > problems. With > special plus tables, triggers and functions now we have a > reasonably fast > database which contains about 5-10 tree structures. If you > are interested, > I may send you some of these part of our database. But, > PostgreSQL "from > base" doesn't support tree structures, neither recursions > (like in SQL3), > as far as I know (see TODO list). >
RE: [SQL] to_char() causes backend to close connection
Title: RE: [SQL] to_char() causes backend to close connection select to_char(now()-'5 minutes'::interval,'YYMMDDHH24MI') seems to work Ben > -Original Message- > From: Kovacs Zoltan Sandor [mailto:[EMAIL PROTECTED]] > Sent: 14 December 2000 15:49 > To: Karel Zak > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] to_char() causes backend to close connection > > > > A problem (IMHO it's not problem) with 'now()::abstime' is > in 7.1 too. > > But why you not use directly now()? > My aim is to get this format for the time '5 minutes ago'. So I need > something like > > select to_char(now()-'5 minutes','YYMMDDHH24MI') > > but this doesn't work due to the missing type conversion. > > Any idea? TIA, Zoltan >
RE: [SQL] readline ??
Title: RE: [SQL] readline ?? Try the new RPMs: postgresql-7.0.3-2mdk. They include readline support. Ben > -Original Message- > From: vs [mailto:[EMAIL PROTECTED]] > Sent: 15 December 2000 10:12 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: [SQL] readline ?? > > > Hope my message doesn't bother you. > I want to use readline with pgsql7.02 on mandrake 7.2. > LM7.2 installed both packages, readline/devel & postgres. > How to make psql know about readline? > > > Thanks > Lucian >
RE: [SQL] Help with 'now', now(), timestamp 'now', ...
Title: RE: [SQL] Help with 'now', now(), timestamp 'now', ...
try timeofday(), now() gives starttime of transaction :-{.
Regards
Ben
PS this has just been discussed in this mailing list or I would have been clueless
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: 27 March 2001 19:02
> To: [EMAIL PROTECTED]
> Subject: [SQL] Help with 'now', now(), timestamp 'now', ...
>
>
> Hi all,
>
>
>
> I´m developing some procedures in my db and i want know
> how much time my
>
>
> procedures take to execute. So, i write my first procedure
> (to test) as
>
> follows:
>
>
>
> CREATE FUNCTION TEST(INTEGER) RETURNS INTEGER AS '
>
> DECLARE
>
> SEQ RECORD;
>
> BEGIN
>
> SELECT NEXTVAL(''TIMES_ID_SEQ'') AS ID INTO SEQ;
>
> INSERT INTO TIMES (ID, START) VALUES (SEQ.ID, NOW());
>
> FOR I IN 1..$1 LOOP ^
>
> INSERT INTO TEST(ANUMBER) VALUES (RANDOM()*$1);
>
> END LOOP;
>
> UPDATE TIMES SET END = NOW() WHERE ID = SEQ.ID;
>
> RETURN SEQ.ID; ^
>
> END;'
>
> LANGUAGE 'PLPGSQL';
>
>
>
> and executed using "SELECT TEST(1);". When it finish,
> i do a "SELECT
>
>
> * FROM TIMES" and got:
>
>
>
> test=> SELECT * FROM TIMES;
>
> start | end | id
>
> --+--+
>
> 10:27:55 | 10:27:55 | 12
>
> 10:27:55 | 10:27:55 | 13
>
> 10:30:29 | 10:30:29 | 14
>
> 10:31:29 | 10:31:29 | 15
>
> (4 rows)
>
>
>
> In id = 12 and id = 13, i runned two times. Then I
> changed the function
>
>
> and run, at id = 14. Change again at id = 15.
>
>
>
> Where is underlined (), i tried to put, 'now',
> timestamp 'now', etc,
>
>
> and always get the same time. What i'm doing wrong?
>
>
>
> obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)
>
> -> TABLE TIMES (ID SERIAL, START TIME, END TIME);
>
> -> PostgreSQL 7.0.2 under Conectiva Linux
>
>
>
>
>
> Thanks,
>
>
>
> Edipo Elder
>
> [[EMAIL PROTECTED]]
>
> _
> Oi! Você quer um iG-mail gratuito?
> Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html
>
>
> ---(end of
> broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
*
This email and any attachments transmitted with it are confidential
and intended solely for the use of the individual or entity to whom
they are addressed. If you have received this email in error please
notify the sender and do not store, copy or disclose the content
to any other person.
It is the responsibility of the recipient to ensure that opening this
message and/or any of its attachments will not adversely affect
its systems. No responsibility is accepted by the Company.
*
RE: [SQL] Max Size of a text field
Title: RE: [SQL] Max Size of a text field You can edit config.h and recompile 7.0.3 to allow for larger tuples (up to 32K - Info used to be in the FAQ but that has changed so try the mailing list archives for info) or you can try 7.1 RC1 or the CVS snapshot as both can deal with almost unlimited text fields ;-) Regards Ben > -Original Message- > From: Koen Antonissen [mailto:[EMAIL PROTECTED]] > Sent: 30 March 2001 12:52 > To: [EMAIL PROTECTED] > Subject: [SQL] Max Size of a text field > > > Hi there > > I just recieved this error: > 'Warning: PostgreSQL query failed: ERROR: Tuple is too big: > size 13872, > max size 8140 ' > > Is there anyting I can do about that other than tell my users just not > typing so much text into the field? > > Kind regards, > Koen Antonissen > > ---(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 > * This email and any attachments transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender and do not store, copy or disclose the content to any other person. It is the responsibility of the recipient to ensure that opening this message and/or any of its attachments will not adversely affect its systems. No responsibility is accepted by the Company. *
[SQL] Problem with Auto Increment
When I try to create a new row in this table and do not explicitly
define a unique value for datadefindex I get the following error
message:
ERROR: Cannot insert a duplicate key into unique index datadef_pkey
Here is the INSERT statement that generated the error:
tmdb=# insert into datadef (cfgmgmtid, datadefname, datadefformat,
datadefunits, datadefdescription)
VALUES (2, 'TestValue', 'REAL', 'N/A', 'This is a test of placing a new
row without an explicit datadefindex');
Here is the table definition:
tmdb=# \d datadef
Table "datadef"
Column | Type |
Modifiers
+---+---
-
datadefindex | integer | not null default
nextval('datadef_
datadefindex_seq'::text)
cfgmgmtid | integer |
datadefname| character varying(80) | not null
datadefformat | character varying(80) | not null
datadefunits | character varying(80) | not null
datadefdescription | text | not null
Primary key: datadef_pkey
Unique keys: datadefname_idx
Triggers: RI_ConstraintTrigger_19507,
RI_ConstraintTrigger_19509,
RI_ConstraintTrigger_19511,
RI_ConstraintTrigger_19513,
RI_ConstraintTrigger_19515,
RI_ConstraintTrigger_19659,
RI_ConstraintTrigger_19661,
RI_ConstraintTrigger_19663,
RI_ConstraintTrigger_19665,
RI_ConstraintTrigger_19667
And finally here is the entry in the datadef_datadefindex_seq table:
tmdb=# select * from datadef_datadefindex_seq;
sequence_name | last_value | increment_by | max_value |
min_value |
cache_value | log_cnt | is_cycled | is_called
--++--++
---+
-+-+---+---
datadef_datadefindex_seq | 8 |1 | 2147483647 |
1 |
1 | 32 | f | t
(1 row)
Notice that last_value = 8, owever the current number of rows in the
datadef table = 67.
My current workaround is to do a MAX(datadefindex) on datadef,
increment it by one and explicitly place that value as the
datradefindex for the new row, however I am worried about the database
stability.
More information : If I DROP the database, recreate it, and enter
values into the table manually, the autoincrement works on this table.
It seems that the problem arises after I reload the data into the table
using the \i command on a file which was created using the pg_dump
command.
I have recently upgraded from 7.2.1 to 7.2.3 using the RPM.
Thanks in advance,
ben
---(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] SQL to list databases?
Is there a query that will return all the databases available, similar to what psql -l does? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Help with pivoting tables
Hi, I am having some diffuculty with pivoting the results of a query. I am using the following SQL in an attempt to aggreate the data from a table. --Begin Query-- SELECT groupid, activity_date, count(CASE activity_subcode WHEN 100 THEN 1 ELSE NULL END) AS Attended, count(CASE activity_subcode WHEN 101 THEN 1 ELSE NULL END) AS Unexcused, count(CASE activity_subcode WHEN 102 THEN 1 ELSE NULL END) AS Absent, count(CASE activity_subcode WHEN 103 THEN 1 ELSE NULL END) AS Called, count(*) AS total FROM activity WHERE activity_date BETWEEN '06/02/2003' AND '06/06/2003' GROUP BY groupid, activity_date, activity_subcode ORDER BY groupid, activity_date --End Query-- The output is coming back like: Groupid activity_date attended unexcused absent called total - BNEIO 2003-06-04 7 0 0 0 7 BNEIO 2003-06-04 0 2 0 0 2 BNEIO 2003-06-05 4 0 0 0 4 BNEIO 2003-06-05 0 5 0 0 5 I need the output to come back with the groups_id and activity_date combined to look like this: Groupid activity_date attended unexcused absent called total - BNEIO 2003-06-04 7 2 0 0 9 BNEIO 2003-06-05 4 5 0 0 9 Any ideas? Thanks, Ben --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Privileged Position
To: [email protected] We will now place any business with a qualified website at the top of the major search engines: Yahoo!, MSN, Alta Vista, etc. This promotion is not going to last long. If you are interested in being guaranteed first position in the top search engines at a promotional fee, please reply to us promptly to find out if you qualify. PLEASE INCLUDE THE URL or WEB SITE ADDRESS YOU ARE INTERESTED IN PROMOTING. This is not pay per click. Examples will be provided.Sincerely, The Positioning Team
Re: [SQL] find all tables with a specific column name?
Is there a reasonable way to extract a list of all tables which contain a specific column name from the system views on 8.1? For instance, I might want to enumerate all tables with a column named last_modified. This is nothing new but if I may, may I add for this thread's completeness a try from internal tables? select a.relkind, a.relname from pg_class a inner join pg_attribute b on a.relfilenode = b.attrelid group by a.relkind, a.relname, a.relfilenode,b.attname having b.attname='IID'; The result didn't match the one from the information_schema.tables - the above query included indexes too (relkind=i) while information_schema.tables included only tables and views (r,v). Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] LinkedList
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. Any information that can point me in the direction to figure this out would be appreciated. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: It may not be exactly suitable, but this one does only traversal (assuming the list is not clsoed) create table linkedlist(prevnode int, nextnode int, val int); -- HEAD insert into linkedlist values(null,1,0); insert into linkedlist values(1,2,10); insert into linkedlist values(2,3,20); insert into linkedlist values(3,4,30); insert into linkedlist values(4,5,40); -- TAIL insert into linkedlist values(5,null,50); -- TRAVERSE begin; declare mc cursor for select * from linkedlist order by nextnode; fetch 1 from mc; fetch 1 from mc; ... close mc; commit; which is nothing more than, select * from linkedlist order by nextnode; Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] LinkedList
On Fri, 28 Apr 2006, Guy Fraser wrote: -- HEAD insert into linkedlist values(null,1,0); insert into linkedlist values(1,2,10); insert into linkedlist values(2,3,20); insert into linkedlist values(3,4,30); insert into linkedlist values(4,5,40); -- TAIL insert into linkedlist values(5,null,50); Bad example of a double linked list, you also need an id for the current node and the values of prevnode and nextnode do not need to be ordered or contiguous as the example shows. Wow. Interesting... I am willing to be corrected, but to me the "node" field seems redundant, since it does not add any information. (Since each item in the list is already uniquely identifiable without the "node".) Certainly so, for traversing, which was the OP's intention. It may save some steps in case of other operations but at the expense of one more field. Please see below. create table linkedlist(node int,prevnode int, nextnode int, val int); insert into linkedlist values(1,null,2,0); insert into linkedlist values(2,1,3,10); insert into linkedlist values(3,2,4,30); insert into linkedlist values(4,3,5,20); insert into linkedlist values(5,4,6,40); insert into linkedlist values(6,5,null,50); If we now wanted to reorder an item in the set you need make some updates in a block, which I have not done before but should be something like this: Move node 4 between 2 and 3 so that the values from head to tail are ordered. update linkedlist set prevnode = '2',nextnode = '3' where node = '4'; update linkedlist set nextnode = '4' where node = '2'; update linkedlist set prevnode = '4' where node = '3'; If the intention is to change it from 0-10-30-20-40-50 to 0-10-20-30-40-50, it would have been (in my design) exchanging node 3 and node 4 below. null,1,0 1,2,10 <-- node 2 2,3,30 <-- node 3 3,4,20 <-- node 4 4,5,40 5,null,50 Now, it can be done by: begin; update linkedlist set prevnode=2 where prevnode=3; -- node 4 = (2,4,20) update linkedlist set prevnode=3 where nextnode=3; -- node 3 = (3,3,30) update linkedlist set nextnode=3 where prevnode=2; -- node 4 = (2,3,20) update linkedlist set nextnode=4 where nextnode=3; -- node 3 = (3,4,30) commit; achieving the same. ... 2,3,20 <-- node 4, originally 3,4,30 <-- node 3, originally ... "node" will be more cost efficient if we insert an item at the beginning of a long list, for example insert (2,3,100) before node 3 (2,3,20), but at least the sql is simple; update linkedlist set prevnode = prevnode + 1 where prevnode > 1; update linkedlist set nextnode = nextnode + 1 where nextnode > 2; and then do insert (2,3,xxx) This method can also be used for reordering. The usefulness of the "node" will depend on the economics of these update operations over keeping one more field. But I think this is more of an exercise, and functions would be the proper way for complex operations. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL]Linked List
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: 1, 0 3, 1 7, 3 9, 7 ... I missed "The table contains many linked lists", so wanted to do another try. I guess there should be a better way, but what if you do this? 1) Assuming your table has two columns (n int, p int), do create table tmplist (n int, p int); 2) drop function traverse(integer); create or replace function traverse (integer) returns integer as $$ declare x int; begin x := $1; while x is not null loop select n into x from linkedlist where p = x; insert into tmplist (select * from links where p=x); -- or do any processing end loop; return 1 ; end; $$ language plpgsql; 3) select traverse(0); select * from tmplist; 0 - 1 - 4 - 8 - 12 ... delete from tmplist; select traverse(2); select * from tmplist; 2 - 3 - 5 - 6 - ... (where 0 or 2 is the heads of the linked lists in the table, which you want to traverse) I'd appreciate any insight if there's a better way but somehow it was not possible to return setof int from within while loop whereas it was possible from within a for loop. I didn't find a way to deliver the templist table name as argument. (Somehow there seemed to be a bug(?) there) Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL]Linked List
The pgsql function is compiled and wouldn't know how to handle a table name as a variable. If you rewrite the SQL to use the 'EXECUTE' statement I think you could do this, something along the lines of (untested): EXECUTE ''INSERT INTO '' || quote_ident(tmplist) || '' (SELECT * FROM links WHERE p=x)''; Thanks. Yet, if I give the table name as the argument, I get the same error. drop function traverse(integer, text); create or replace function traverse (integer, text) returns integer as $$ declare x int; tname alias for $2; begin x := $1; while x is not null loop select n into x from links where p = x; insert into tmplink (select * from links where p=x); EXECUTE ''INSERT INTO '' || quote_ident(tname) || '' (SELECT * FROM links WHERE p=x)''; end loop; return 1 ; end; $$ language plpgsql; The above gives the following error. Please note that the first and second args are mixed up now. I tested it only on 8.0 (FC 4) and didn't on 8.1 and wonder whether it's a bug or I may be doing something wrong. Using tname or $2 doesn't change the result. # select traverse(0, 'links2'); ERROR: syntax error at or near "INSERT" at character 11 QUERY: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT * FROM links WHERE p= $2 )'' CONTEXT: PL/pgSQL function "traverse" line 10 at execute statement LINE 1: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT ... 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] 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] 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
Re: [SQL] i am getting error when i am using copy command
file '/tmp/penchal.out' for writing: no such file or directory.. _IF_ you're on linux it could be due to selinux. setenforce 0 might solve the problem tempoarily. I would assume there should've been some existing discussion threads. (setenforce 1 afterwards.) Regards, Ben K. ---(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] audit table containing Select statements submitted
Current_user Timestamp "The Select Statement Submitted by the User" http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE might be close to what you want. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Returning String as Integer
Jorge Godoy <[EMAIL PROTECTED]> wrote: numbers. Is there any way to select a value from this column and return it as an integer? My twopence. I just happened to have the same problem with iReports for a 10 digit number - it may be case specific but in my case # select ... int4(id) ...; worked. I tried int8(id) but java didn't take it as Integer. (It took int8 as something like Long.) Regards, Ben ---(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] Returning String as Integer
My twopence. I just happened to have the same problem with iReports for a 10 digit number - it may be case specific but in my case Please, take care with your quote attributions. I've never asked such a question here and, in fact, I was answering it. The person who asked such a question was Kashmira Patel. Sorry, I apologize. Regards, Ben K. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Most efficient way to hard-sort records
main_table: id, name, position key_table: id, main_table_id, key, value Here is how I need to sort the records: SELECT * FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER BY value I currently collect all ids from main_table in sorted order and then update the position field for each row in the main_table one-by-one. Is there a better/faster/more efficient solution? A cheap solution if you don't care about the position value as long as sort order is ok. 1) # SELECT main_table.id into temp_table FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id ORDER BY value; 2) # update main_table set position = (select oid from temp_table where id = main_table.id ); I guess I'll get a set of consecutive oids by this. You can make the number begin at arbitrary number, by 2-a) # update main_table set position = ( (select oid::int4 from temp_table where id = main_table.id ) - (select min(oid::int4) from temp_table) + 1) ; I read that oid wraps around (after ~ billions) so you might want to check your current oid. 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] Most efficient way to hard-sort records
CREATE TABLE sorted (order_no SERIAL PRIMARY KEY, other columns...)
INSERT INTO sorted (columns) SELECT * FROM main_table INNER JOIN
key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER
BY value SELECT
The SERIAL will automatically generate the order_no you want, which
corresponds to the position in the sorted set.
Then, to get the records in-order :
SELECT * FROM sorted ORDER BY order_no
Good ... I just got myself into the habit of not recreating a table since
I have to clean up permissions and what not. I guess it depends.
Another version along that line ?
# create sequence counterseq start 1;
-- (set/reset whenever a counter is needed)
# select main_table.*, nextval('counterseq') as position2
into sorted_main_table
from main_table, keytable where main_table.id =
keytable.main_table_id
order by value;
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] keeping last 30 entries of a log table
I need to write a function which inserts a log entry in a log table and only
keeps the last 30 records. I was thinking of using a subquery similar to the
following:
insert into log (account_id, message) values (1, 'this is a test);
delete from log where account_id = 1 and id not in ( select id from log
where account_id = 1 order by timestamp desc limit 30);
I'm wondering if there is a more performance oriented method of doing the
delete that I'm not thinking of.
Just for the sake of alternatives -
create sequence cy30 maxvalue 30 cycle;
insert into log values(select generate_series(1,30), 'dummy');
INSERT 0 30
update log set des='' where account_id=(select nextval('cy30'));
UPDATE 1
There are details to consider I guess. For example what if an update fails
and the sequence already advanced... Also, since we cycle the id, for
sorting, we'll need to add timestamp or something similar.
My 2 pence...
P.S.
This A) failed me and I wonder if this is supposed to be so or if it's
just a place where no one treaded on ??
B) works fine except it doesn't advance the sequence.
A) update tc set des='b' where id=nextval('cy30')::int;
UPDATE 30
B) update tc set des='c' where id=currval('cy30');
UPDATE 1
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Help : insert a bytea data into new table
Quoth dennis : > > I need to copy some data to new table. > But I encounter some error message. > the table structure > Table A: > c1 char > c2 bytea > > Table B: > c1 char > c2 bytea > > > My sql command: > insert into B as select * from a where c1=xxx 'AS' isn't valid there. What is xxx? Is it a field you haven't shown us, or is it a quoted string? > error: > operator does not exist: text || bytea That command (with 'xxx' quoted and the AS removed) doesn't give that error with those table definitions, so you will need to show us your actual query. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
Quoth dennis : > here is example > > table name is "mail": > column| type > - > sender|char > subject |char I presume you mean 'varchar'? > content |bytea > > > I want copy some record into new table 'mail_new'. > > sql: > create table mail_new as select * from mail sender='dennis' You omitted the WHERE. It's very hard to see what's actually going on when you keep mis-typing the commands you used. > result has an error: > operator does not exist: text || bytea > > > But if my sql statement has no column "content" > the sql works. > sql: > create table mail_new as select sender,subject from mail sender='dennis' No, it still doesn't give that error for me. Show us something you've *actually* *tried*. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
Quoth dennis :
> Hi Ben
>
> here is my function , it's for fix missing chunk problem.
> It has same problem ,please take look
>
>
> thank for you help
>
> -table--
>
>
> db=# \d usersessiontable;
> Table "public.usersessiontable"
>Column | Type | Modifiers
> ---++---
> serverid | character varying(100) |
> sessionid | character varying(50) |
> data | bytea |
> Indexes:
> "usersessiontable_idx" btree (sessionid)
> db=#
>
> db=# \d usersessiontable_test;
> Table "public.usersessiontable"
>Column | Type | Modifiers
> ---++---
> serverid | character varying(100) |
> sessionid | character varying(50) |
> data | bytea |
>
> --function
>
>
> CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable()
>RETURNS integer AS
> $BODY$
> declare
> begin
> records = 0;
> OPEN curs1 FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY
> sessionid';
> loop
> FETCH curs1 INTO rowvar;
> IF NOT FOUND THEN
> EXIT;
> END IF;
> begin
> a_sql = 'insert into
> usersessiontable_test(sessionid,serverid,data)
> values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';<<--my
>
You are trying to concatenate ''',E''' (of type text) and rowvar.data
(of type bytea). This is where the error is coming from. (This actually
works in 8.4, so I presume you're using an earlier version?) In any
case, this is not a safe way to interpolate into an SQL string: you need
the quote_literal function.
a_sql = 'insert into usersessiontable (sessionid, serverid, data) '
|| 'values (' || quote_literal(rowvar.sessionid) || ', '
|| quote_literal(rowvar.serverid) || ', '
|| quote_literal(rowvar.data) || ')';
(Is there a function which will do %-interpolation the way RAISE does?
It would be much clearer in cases like this.)
Ben
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
Quoth dennis : > Dear Ben > > thanks for you anwser. > I try to add function quote_literal on my sql statement . > > but it raise other error message (quote_literal not support bytea format): > function quote_literal(bytea) does not exist Which Postgres version are you using? Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
[quoting fixed]
Quoth dennis :
> Ben Morrow wrote:
> > Quoth dennis:
> >> Dear Ben
> >>
> >> thanks for you anwser.
> >> I try to add function quote_literal on my sql statement .
> >>
> >> but it raise other error message (quote_literal not support bytea format):
> >>function quote_literal(bytea) does not exist
> >
> > Which Postgres version are you using?
>
> Postgres : 8.1.4
Then I think you want
create function quote_literal (bytea)
returns text
immutable strict
language plpgsql
as $$
begin
return 'E'''
|| replace(encode($1, 'escape'), E'\\', E'')
|| '''';
end;
$$;
Ben
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] count function alternative in postgres
Quoth [email protected] (junaidmalik14): > > Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in > postgres. We get error if we > > write count like this count(distinct profile.id, profile.name, profile.age) > but it works well in mysql. Pg does support COUNT(DISTINCT ), but only for a single column. The best I can come up with for multiple columns is select count(distinct profile.tuple) from (select (id, name, age) as tuple from profile) as profile; or alternatively select count(*) from (select distinct (id, name, age) as tuple from profile) as profile; Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Rules and sequences
I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
However, I'm running into problems when one of the audited tables has a
'serial' column that is allowed to default:
create table foo (id serial, bar text);
create table audit (ix bigserial, rec text);
create rule audit_insert as on insert to foo do also
insert into audit (rec) values ((new.*)::text);
insert into foo (bar) values ('baz');
select * from foo;
id | bar
+-
1 | baz
(1 row)
select * from audit;
ix | rec
+-
1 | (2,baz)
(1 row)
I can see why this is happening (the rule is essentially a macro, so the
NEW expression gets expanded twice, including the nextval call, so the
sequence is incremented twice), but is there any way to prevent it? Some
way of 'materialising' the NEW row so it is just plain values rather
than a list of expressions?
Ben
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rules and sequences
Quoth [email protected] (Tom Lane): > Ben Morrow writes: > > I am trying to implement a fairly standard 'audit table' setup, but > > using rules instead of triggers (since it should be more efficient). > > Rules are sufficiently tricky that I would never, ever rely on them for > auditing. Use a simple AFTER trigger instead. OK, thanks. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Casts in foreign schemas
Is this behaviour expected? (This is with 8.4.3 on FreeBSD.) create schema one; set search_path to one; create type foo as (x integer); create function foo (integer) returns foo language plpgsql as $$ declare y foo; begin y.x = $1; return y; end $$; create cast (integer as foo) with function foo (integer); grant usage on schema one to public; grant execute on function foo (integer) to public; create schema two; -- reconnect as a different user set search_path to two; select 3::one.foo; ERROR: type "foo" does not exist CONTEXT: compilation of PL/pgSQL function "foo" near line 2 set search_path to two, one; select 3::foo; foo - (3) (1 row) My understanding of things was that PL/pgSQL functions were compiled at CREATE FUNCTION time, using the SEARCH_PATH currently in effect. Is that wrong? Is there some GRANT I'm missing that will make this work? Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] enforcing constraints across multiple tables
Quoth [email protected] (Andrew Geery): > > I have a question about checking a constraint that is spread across multiple > (in the example below, two) tables. In the example below, every food (food > table) is in a food group (food_group table). For every person (person > table), I want to enforce the constraint that there can only be one food in > a given food group (person_food link table) [think of it as every person may > have a favorite food in a given food group]. > > The problem seems to be that the link is in the person_food table, but the > information that is needed to verify the constraint is also in the food > table (i.e., what food group is the food in?). > > There are two problems here: > (1) don't allow a food to be associated with a person if there is already a > food in the same food group associated with the person; and > (2) don't allow the food group for a food to be changed if this would > violate (1) > > To enforce (1), I created a function to check whether a given food can be > associated with a given person (is there already a food in the same food > group associated with the person?) and added a check constraint to the > person_food table. > To enforce (2), I wasn't able to use a check constraint because the > constraint was being checked with the existing data, not with the new data. > I had to add an after trigger that called a function to do the check. > > My questions are: > (A) Is there a way to check (2) above using a constraint and not a trigger? > (B) Is there an easier way to solve this problem? Does the complicated > nature of the solution make the design poor? > (C) Should I not worry about this constraint at the DB level and just > enforce it at the application level? > > Below are the tables, functions and triggers I was using. > > Thanks! > Andrew > > === > > create table person ( >id serial primary key, >name varchar not null > ); > > create table food_group ( >id serial primary key, >name varchar not null > ); > > create table food ( >id serial primary key, >food_group_id int not null references food_group, >name varchar not null > ); > > create table person_food ( >person_id int not null references person, >food_id int not null references food, >primary key (person_id, food_id), >check (is_person_food_unique(person_id, food_id)) > ); Instead of this, try create table person_food ( person_id int not null references person, food_id int not null, food_group_id int not null, foreign key (food_id, food_group_id) references food (id, food_group_id), unique (person_id, food_group_id) ); If you wish to move foods between groups, the foreign key above will need to be ON UPDATE CASCADE. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
Quoth [email protected] (John): > On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote: > > I would be looking at the log files for the Inserts into that table as a > > means to track down what is the cause. If there are no log files or > > don't have enough detail, crank up the logging level and wait for it to > > happen again??? > > > That is scary - let it happen again I'm not keeping enough info in the > log. I actually turned off most of the info the log files are gathering > because the system has been running for 6-7 months without an issue. I just > got a call around noon telling me something was going wrong. That's when I > discovered the sequences were the wrong values. I'm sure there has to be > some sort of real explanation - but I don't know what it is. There are several possible causes: - Something somewhere is inserting values directly into the serial columns, without using the sequence. This can be prevented by REVOKEing INSERT and UPDATE on the relevant columns for all users. If this causes problems anywhere in your app, those are good places to start looking for bugs. - Something somewhere is manipulating the sequence. This can be prevented by REVOKEing UPDATE on all sequences for all users. You may need some additional GRANTs of USAGE on sequences if parts of the app were relying on UPDATE to call nextval(). Obviously if your app routinely drops and creates tables you will need to arrange for these permissions to be applied every time. - The database has become corrupted, perhaps by a badly-done backup and restore. (I would not expect taking a backup alone to cause corruption, but if the backup isn't done right the backed-up copy may be corrupt.) Have you done a restore recently? - Something I haven't thought of :). - A bug in Pg. While this is *extremely* unlikely, it must be mentioned as a possibility. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using min|max in where
Hi, I have some sql like so: SELECT min(date) INTO d FROM interest_rate WHERE m_code = NEW.code; UPDATE interest_rate SET date = NEW.start_date, rate = NEW.initial_rate WHERE m_code = NEW.code AND date = d; Actually this is pgsql but I don't think that matters. I am wondering if I can make this more compact by somehow including the 'min' function in the WHERE clause, but WITHOUT simply moving the select in there. So not this.. UPDATE interest_rate SET date = NEW.start_date, rate = NEW.initial_rate WHERE m_code = NEW.code AND date = (SELECT min(date) FROM interest_rate WHERE m_code = NEW.code); This is just an example but I seem to find this pattern a lot in my functions. B
[SQL] Volatile functions in WITH
Suppose I run the following query:
WITH "exp" AS (
DELETE FROM "item" i
USING "item_expired" e
WHERE e.oref = i.ref
AND i.basket= $1
RETURNING i.basket, e.oref, e.nref, i.count, e.msg
),
"subst" AS (
INSERT INTO "item" ("basket", "ref", "count")
SELECT e.basket, e.nref, e.count
FROM "exp" e
WHERE e.nref IS NOT NULL
)
SELECT DISTINCT e.msg
FROM "exp" e
This is a very convenient and somewhat more flexible alternative to
INSERT... DELETE RETURNING (which doesn't work). However, the "item"
table has a unique constraint on (basket, ref), so sometimes I need to
update instead of insert; to handle this I have a VOLATILE function,
add_item. Unfortunately, if I call it the obvious way
WITH "exp" AS ( -- as before
),
"subst" AS (
SELECT add_item(e.basket, e.nref, e.count)
FROM "exp" e
WHERE e.nref IS NOT NULL
)
SELECT DISTINCT e.msg
FROM "exp" e
then the planner sees that the results of "subst" are not used, and
doesn't include it in the query plan at all.
Is there any way I can tell WITH that add_item is actually a data-
modifying statement? Adding FOR UPDATE doesn't seem to help (I didn't
really expect it would.)
Alternatively, are either of these safe (that is, are they guaranteed to
call the function once for every row returned by "exp", even if the
DISTINCT ends up eliminating some of those rows)?
WITH "exp" AS ( -- as before
), "subst" AS ( -- SELECT add_item(...) as before
)
SELECT DISTINCT e.msg
FROM "exp" e
LEFT JOIN "subst" s ON FALSE
WITH "exp" AS ( -- as before
)
SELECT DISTINCT s.msg
FROM (
SELECT e.msg, CASE
WHEN e.nref IS NULL THEN NULL
ELSE add_item(e.basket, e.nref, e.count)
END "subst"
) s
I don't like the second alternative much, but I could live with it if I
had to.
Ben
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] upsert doesn't seem to work..
Quoth [email protected] (Bert): > > We continuously load data from flat files in our database. > We first insert the data into unlogged tables (in the loadoltp schema), and > then we use the 'upsert' statement to transfer the data from the load table > into the tables we are going to use. > > The load tables are unlogged, and don't have indexes / pk's on them. All > our 'real tables', which contains the data, always have a pk consisting out > of 2 fields. In the example those are 'tick_server_id' and 'item_id'. > > At first everything seems to run ok, however it seems that new fields > aren't always inserted as desired. > > > This is an example query which causes troubles: That query is basically equivalent to something like create table "st_item" ( server_id integer, item_id integer, item_desc text, primary key (server_id, item_id) ); create table "st_item_insert" ( server_id integer, item_id integer, item_desc text ); with "upsert" as ( update "st_item" et set "item_desc" = e.item_desc from "st_item_insert" e where et.server_id = e.server_id and et.item_id = e.item_id returning et.server_id, et.item_id ) insert into "st_item" ("server_id", "item_id", "item_desc") select et.server_id, et.item_id, et.item_desc from "st_item_insert" et where et.server_id not in ( select et.server_id from "upsert" b) and et.item_id not in ( select et.item_id from "upsert" b) There are three problems here. The first is that the NOT IN subselect selects from et instead of from b. In the context of this subselect "et" is a table reference from outside the subselect, so it's treated as a constant for each run of the subselect. That means that the subselect will return the value you are testing against for every row in "upsert", so if there were any updates at all you will make no insertions. The second is that you are making two separate subselects. This means that a row in st_item_insert will not be inserted if there is a row in "upsert" with a matching server_id and a row in "upsert" with a matching item_id, *even if they are different rows*. For instance, suppose st_item_insert has 2 1 foo 1 2 bar 2 2 baz and the 'foo' and 'bar' entries get updated. The 'baz' entry will then not get inserted, because the first subselect will find the 'foo' row and the second will find the 'bar' row. What you need is a single row subselect, like this: where (et.server_id, et.item_id) not in ( select "server_id", "item_id" from "upsert") The third is that upsert is not as simple as you think. It isn't possible (at least, not in Postgres) to take a lock on a row which doesn't exist, so it's possible that a concurrent transaction could insert a row with a conflicting key between the time the UPDATE runs and the time the INSERT runs. You need to either lock the whole table or use the retry strategy documented in the 'Trapping Errors' section of the PL/pgSQL documentation. Annoyingly, even 9's serializable transactions don't seem to help here, at least not by my experiments. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Perform Function When The Rows Of A View Change
Quoth [email protected] (Adam): > > I have a rather complicated view that is dependent upon multiple > tables, consisting of several windowing and aggregate functions, as > well as some time intervals. I would like to be able to perform a > function, i.e. pg_notify(), whenever a row is added, changed, or > removed from the view's result set. > > I think the kicker is the fact that the set of results returned by the > view is dependent on the current time. > > Here's a simplified version of what's going on: > > CREATE VIEW view2 AS ( > SELECT view1.id, view1.ts >FROM view1 > WHERE view1.ts > (now() - '1 day'::interval) > ); > > As such, even if there are no inserts, deletes, or updates performed > on any of the tables that view1 depends on, the data contained in > view2 will change as a function of time (i.e. rows will disappear > from the view as time elapses). I have been unable to come up with a > trigger or rule that can detect this situation and provide the > notification I'm looking for. > > I could just query the view over and over again, and look for changes > as they occur. But I'm hoping to find a more elegant (and less > resource-intensive) solution. Any ideas? Well, in principle you could calculate the next time the view will change assuming the tables don't change first, and have a client sit there sleeping until that time. For instance, the view you posted will next change at select min(t.ts) from ( select view1.ts + '1 day'::interval "ts" from view1 ) t where t.ts > now() unless the tables view1 is based on change first. Apart from the potential difficulty calculating that time, you would need to be able to wake up that client early if one of the tables changed. Setting triggers on the tables to send a notify to that client (probably a different notify from the one that client then sends out to other clients) should be sufficient, as long as that client uses select(2) and PQconsumeInput to make sure it receives the notifications in a timely fashion. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to reject overlapping timespans?
Quoth [email protected] (Andreas): > Am 17.02.2013 19:20, schrieb Andreas Kretschmer: > > Andreas hat am 17. Februar 2013 um 18:02 geschrieben: > >> I need to store data that has a valid timespan with start and enddate. > >> > >> objects ( id, name, ... ) > >> object_data ( object_id referencs objects(id), startdate, enddate, ... ) > >> > >> nothing special, yet > >> > >> How can I have PG reject a data record where the new start- or enddate > >> lies between the start- or enddate of another record regarding the same > >> object_id? > > > > With 9.2 you can use DATERANGE and exclusion constraints > > though I still have a 9.1.x as productive server so I'm afraid I have to > find another way. If you don't fancy implementing or backporting a GiST operator class for date ranges using OVERLAPS, you can fake one with the geometric types. You will need contrib/btree_gist to get GiST indexes on integers. create extension btree_gist; create function point(date) returns point immutable language sql as $$ select point(0, ($1 - date '2000-01-01')::double precision) $$; create function box(date, date) returns box immutable language sql as $$ select box(point($1), point($2)) $$; create table objects_data ( object_id integer references objects, startdate date, enddate date, exclude using gist (object_id with =, box(startdate, enddate) with &&) ); You have to use 'box' rather than 'lseg' because there are no indexes for lsegs. I don't know how efficient this will be, and of course the unique index will probably not be any use for anything else. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Volatile functions in WITH
Quoth [email protected] (Sergey Konoplev): > On Sat, Feb 16, 2013 at 11:58 PM, Ben Morrow wrote: > > WITH "exp" AS ( -- as before > > ), > > "subst" AS ( > > SELECT add_item(e.basket, e.nref, e.count) > > FROM "exp" e > > WHERE e.nref IS NOT NULL > > ) > > SELECT DISTINCT e.msg > > FROM "exp" e > > Alternatively I suppose you can try this one: > > WITH "exp" AS ( > DELETE FROM "item" i > USING "item_expired" e > WHERE e.oref = i.ref > AND i.basket = $1 > RETURNING i.basket, e.oref, e.nref, i.count, e.msg > ), > "upd" AS ( > UPDATE "item" SET "count" = e.count > FROM "exp" e > WHERE e.nref IS NOT NULL > AND ("basket", "nref") IS NOT DISTINCT FROM (e.basket, e.nref) > RETURNING "basket", "nref" > ) >"ins" AS ( > INSERT INTO "item" ("basket", "ref", "count") > SELECT e.basket, e.nref, e.count > FROM "exp" e LEFT JOIN "upd" u > ON ("basket", "nref") IS NOT DISTINCT FROM (e.basket, e.nref) > WHERE e.nref IS NOT NULL AND (u.basket, u.nref) IS NULL > ) > SELECT DISTINCT e.msg > FROM "exp" e That's not reliable. A concurrent txn could insert a conflicting row between the update and the insert, which would cause the insert to fail with a unique constraint violation. > > then the planner sees that the results of "subst" are not used, and > > doesn't include it in the query plan at all. > > > > Is there any way I can tell WITH that add_item is actually a data- > > modifying statement? Adding FOR UPDATE doesn't seem to help (I didn't > > really expect it would.) > > In this regard I would like to listen to gugrus' opinion too. > > EXPLAIN ANALYZE WITH t AS (SELECT random()) SELECT 1; > QUERY PLAN > > Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 > rows=1 loops=1) > Total runtime: 0.063 ms > (2 rows) > > EXPLAIN ANALYZE WITH t AS (SELECT random()) SELECT 1 from t; > QUERY PLAN > > CTE Scan on t (cost=0.01..0.03 rows=1 width=0) (actual > time=0.048..0.052 rows=1 loops=1) >CTE t > -> Result (cost=0.00..0.01 rows=1 width=0) (actual > time=0.038..0.039 rows=1 loops=1) > Total runtime: 0.131 ms > (4 rows) > > I couldn't manage to come to any solution except faking the reference > in the resulting query: > > WITH t AS (SELECT random()) SELECT 1 UNION ALL (SELECT 1 FROM t LIMIT 0); Yes, I can do experiments too; the alternatives I gave before both work on my test database. What I was asking was whether they are guaranteed to work in all situations, given that the planner can in principle see that the extra table reference won't affect the result. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Volatile functions in WITH
At 8AM -0800 on 20/02/13 you (Sergey Konoplev) wrote: > On Wed, Feb 20, 2013 at 12:19 AM, Ben Morrow wrote: > > That's not reliable. A concurrent txn could insert a conflicting row > > between the update and the insert, which would cause the insert to fail > > with a unique constraint violation. > > Okay I think I got it. The function catches exception when INSERTing > and does UPDATE instead, correct? Well, it tries the update first, but yes. It's pretty-much exactly the example in the PL/pgSQL docs. > If you got mixed up with plpgsql anyway what is the reason of making > this WITH query constructions instead of implementing everything in a > plpgsql trigger on DELETE on exp then? I'm not sure what you mean. "exp" isn't a table, it's a WITH CTE. The statement is deleting some entries from "item", and replacing some of them with new entries, based on the information in the "item_expired" view. I can't do anything with a trigger on "item", since there are other circumstances where items are deleted that shouldn't trigger replacement. > > Yes, I can do experiments too; the alternatives I gave before both work > > on my test database. What I was asking was whether they are guaranteed > > to work in all situations, given that the planner can in principle see > > that the extra table reference won't affect the result. > > From the documentation "VOLATILE indicates that the function value can > change even within a single table scan, so no optimizations can be > made". So they are guaranteed to behave as you need in your last > example. Well, that's ambiguous. The return value can change even within a single scan, so if you want 3 return values you have to make 3 calls. But what if you don't actually need one of those three: is the planner allowed to optimise the whole thing out? For instance, given select * from (select j.type, random() r from item j) i where i.type = 1 the planner will transform it into select i.type, random() r from item i where i.type = 1 before planning, so even though random() is volatile it will only get called for rows of item with type = 1. I don't know if this happens, or may sometimes happen, or might happen in the future, for rows eliminated because of DISTINCT. (I think perhaps what I would ideally want is a PERFORM verb, which is just like SELECT but says 'actually calculate all the rows implied here, without pulling in additional filter conditions'. WITH would then have to treat a top-level PERFORM inside a WITH the same as DML.) Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Volatile functions in WITH
At 12PM -0800 on 20/02/13 you (Sergey Konoplev) wrote: > On Wed, Feb 20, 2013 at 10:16 AM, Ben Morrow wrote: > >> If you got mixed up with plpgsql anyway what is the reason of making > >> this WITH query constructions instead of implementing everything in a > >> plpgsql trigger on DELETE on exp then? > > > > I'm not sure what you mean. "exp" isn't a table, it's a WITH CTE. The > > Sorry, I meant "item" of course, "exp" was a typo. OK. > > statement is deleting some entries from "item", and replacing some of > > them with new entries, based on the information in the "item_expired" > > view. I can't do anything with a trigger on "item", since there are > > other circumstances where items are deleted that shouldn't trigger > > replacement. > > Okay, I see. > > If the case is specific you can make a simple plpgsql function that > will process it like FOR _row IN DELETE ... RETORNING * LOOP ... > RETURN NEXT _row; END LOOP; Yes, I *know* I can write a function if I have to. I can also send the whole lot down to the client and do the inserts from there, or use a temporary table. I was hoping to avoid that, since the plain INSERT case works perfectly well. > > select * > > from (select j.type, random() r from item j) i > > where i.type = 1 > > > > the planner will transform it into > > > > select i.type, random() r > > from item i > > where i.type = 1 > > > > before planning, so even though random() is volatile it will only get > > called for rows of item with type = 1. > > Yes, functions are executed depending on the resulting plan "A query > using a volatile function will re-evaluate the function at every row > where its value is needed". > > > I don't know if this happens, or may sometimes happen, or might happen > > in the future, for rows eliminated because of DISTINCT. > > It is a good point. Nothing guarantees it in a perspective. Optimizer > guarantees a stable result but not the way it is reached. Well, it makes functions which perform DML a lot less useful, so I wonder whether this is intentional behaviour. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Summing & Grouping in a Hierarchical Structure
Quoth [email protected] (Don Parris): > > Is it possible to use spaces in the ltree path, like so: > TOP.Groceries.Food.Herbs & Spices > > Or do the elements of the path have to use underscores and dashes? >From the docs: | A label is a sequence of alphanumeric characters and underscores (for | example, in C locale the characters A-Za-z0-9_ are allowed). Labels | must be less than 256 bytes long. [...] | | A label path is a sequence of zero or more labels separated by dots, | for example L1.L2.L3, representing a path from the root of a | hierarchical tree to a particular node. The length of a label path | must be less than 65Kb, but keeping it under 2Kb is preferable. If you need to store non-alphanumeric labels, one answer (as long as they aren't too long) would be to use URL-encoding, like TOP.Groceries.Food.Herbs_20_26_20Spices Of course, you would need to encode _ as well, and you would need to be sure the labels weren't going to come out too long. Another alternative would be to MD5 each label and use (say) the first 10 bytes of that MD5 in hex as the ltree label. (Annoyingly there's only one non-alphanumeric, so you can't use base64.) If you were going to do that you would need to consider the possibility of an attacker arranging a hash collision: I don't know where you're labels come from, so I don't know if this would be an issue. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a new database with a TEMPLATE did not work
Quoth [email protected] (Adrian Klaver): > On 02/25/2013 02:49 PM, mkumbale wrote: > > Hi, I am new to PostgreSQL. I have an empty PostgreSQL DB containing tables > > but no data. I issued the following command in PGADMIN SQL editor: > > > > CREATE DATABASE "NewDefault" > >WITH OWNER = postgres > > ENCODING = 'UTF8' > > TEMPLATE = Default > > TABLESPACE = pg_default > > LC_COLLATE = 'English_United States.1252' > > LC_CTYPE = 'English_United States.1252' > > CONNECTION LIMIT = -1; > > > > Although it created the NewDefault DB, it does not contain any of the tables > > in Default. Default was disconnected when I executed this command. > > > > What am I doing something wrong? > > So you have a database named Default? > > Probably not a good name because: > http://www.postgresql.org/docs/9.2/interactive/sql-createdatabase.html > > template > The name of the template from which to create the new database, or > DEFAULT to use the default template (template1). > > So at a guess you are actually creating the new database from template1. Also, SQL names are folded to lowercase unless they are quoted, so TEMPLATE = Default would refer to a database called "default" (if it weren't special syntax for template1), but TEMPLATE = "Default" should find a database created with CREATE DATABASE "Default". Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help revoking access WHERE state = 'deleted'
Quoth [email protected] (Mark Stosberg): > > We are working on a project to start storing some data as "soft deleted" > (WHERE state = 'deleted') instead of hard-deleting it. > > To make sure that we never accidentally expose the deleted rows through > the application, I had the idea to use a view and permissions for this > purpose. > > I thought I could revoke SELECT access to the "entities" table, but then > grant SELECT access to a view: > > CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state > != 'deleted'; > > We could then find/replace in the code to replace references to the > "entities" table with the "entities_not_deleted" table (If you wanted to you could instead rename the table, and use rules on the view to transform DELETE to UPDATE SET state = 'deleted' and copy across INSERT and UPDATE...) > However, this isn't working, I "permission denied" when trying to use > the view. (as the same user that has had their SELECT access removed to > the underlying table.) Works for me. Have you made an explicit GRANT on the view? Make sure you've read section 37.4 'Rules and Privileges' in the documentation, since it explains the ways in which this sort of information hiding is not ironclad. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help revoking access WHERE state = 'deleted'
Quoth [email protected] (Wayne Cuddy): > On Thu, Feb 28, 2013 at 06:02:05PM +0000, Ben Morrow wrote: > > > > (If you wanted to you could instead rename the table, and use rules on > > the view to transform DELETE to UPDATE SET state = 'deleted' and copy > > across INSERT and UPDATE...) > > Sorry to barge in but I'm just curious... I understand this part > "transform DELETE to UPDATE SET state = 'deleted'". Can you explain a > little further what you mean by "copy across INSERT and UPDATE..."? I should first say that AIUI the general recommendation is to avoid rules (except for views), since they are often difficult to get right. Certainly I've never tried to use rules in a production system. That said, what I mean was something along the lines of renaming the table to (say) entities_table, creating an entities view which filters state = 'deleted', and then create rule entities_delete as on delete to entities do instead update entities_table set state = 'deleted' where key = OLD.key; create rule entities_insert as on insert to entities where NEW.state != 'deleted' do instead insert into entities_table select NEW.*; create rule entities_update as on update to entities where NEW.state != 'deleted' do instead update entities_table set key = NEW.key, state = NEW.state, field1 = NEW.field1, field2 = NEW.field2 where key = OLD.key; (This assumes that "key" is the PK for entities, and that the state field is visible in the entities view with values other than 'deleted'. I don't entirely like the duplication of the view condition in the WHERE clauses, but I'm not sure it's possible to get rid of it.) This is taken straight out of the 'Rules on INSERT, UPDATE and DELETE' section of the documentation; I haven't tested it, so it may not be quite right, but it should be possible to make something along those lines work. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] xmlelement name
Hi,
I'm trying to write a function that will take a name as a text value,
and return an XML element with that name as name, like so:
create function xpercent(nam text, val int) returns xml as $$
begin
return ( select xmlelement(name nam, concat(val::text, '%')) );
end;
$$ language plpgsql;
But when I call the function, nam is used as the name instead of what
the parameter nam contains:
select xpercent('hello', 4);
xpercent
---
4%
(1 row)
How can I get this to work so I get 4%http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UPDATE query with variable number of OR conditions in WHERE
Quoth [email protected] (JORGE MALDONADO): > > I am building an UPDATE query at run-time and one of the fields I want to > include in the WHERE condition may repeat several times, I do not know how > many. > > UPDATE table1 > SET field1 = "some value" > WHERE (field2 = value_1 OR field2 = value_2 OR .OR field2 = value_n) > > I build such a query using a programming language and, after that, I > execute it. Is this a good approach to build such a query? You can use IN for this: UPDATE table1 SET field1 = "some value" WHERE field2 IN (value_1, value_2, ...); Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ZIP function
Quoth [email protected] (Jasen Betts): > On 2013-03-16, Victor Sterpu wrote: > > > > Is there a function that will give the resulting zip content for a=20 > > string? > > Like SELECT zip('test data');? > > no. you could write one that calls gzip in one of the untrusted > languages. or in C you could call zlib. You can call Compress::Zlib or Archive::Zip (depending on which sort of 'zip' you mean) from (trusted) PL/Perl, provided you load the modules from plperl.on_init. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] From with case
Quoth [email protected] (Mauricio Cruz): > > I'm working in a PL/SQL and I'd like to use the same > PL for 2 kinds of tables... > > I have "valepag" and "valerec" both tables > have the same columns, but one is for debit and the other one is for > credit, the PL will work for both cases > > with the unique diference for > the name of the table... > > So I thought to use something like this: > ... > > For rSql in select a.adiant, > a.desc_per > from case > when > cTip='P' > then valapag > else valerec > end > where cod=2 Loop > > ... > > But > it just dont work... does some one have other solution for this case ? I would use a view for this: create view vale_any as select 'P'::text "type", v.adiant, v.desc_per, v.cod from valepag v union all select 'R', v.adiant, v.desc_per, v.cod from valerec v; then for rSql in select a.adiant, a.desc_per from vale_any a where a.type = cTip and a.cod = 2 loop You need to cast the constant in the view definition, otherwise Pg complains about its type being ambiguous. You should use the same type as cTip will be. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] From with case
Quoth [email protected] (Pavel Stehule): > Dne 25.3.2013 23:51 "Ben Morrow" napsal(a): > > > > I would use a view for this: > > > > create view vale_any as > > select 'P'::text "type", v.adiant, v.desc_per, v.cod > > from valepag v > > union all > > select 'R', v.adiant, v.desc_per, v.cod > > from valerec v; > > > > then > > > > for rSql in > > select a.adiant, a.desc_per > > from vale_any a > > where a.type = cTip and a.cod = 2 > > loop > > This design has a performance problem. You read both tables everywhere - > for large tables can be bad You would think so, but, in general, Pg is cleverer than that. For the simple case of queries with constants in (so, a client-submitted query like select * from vale_any a where a.type = 'P' and a.cod = 2 or the equivalent with bound placeholders) the planner won't even plan the parts of the view which don't get used. Try some experiments with EXPLAIN to see what I mean: the unused sections of the Append (that is, the UNION ALL) are either omitted entirely or get replaced with Result One-Time Filter: false (I'm not entirely sure what makes the difference, though it seems to be to do with how complicated the individual parts of the UNION are). PL/pgSQL is a bit more complicated, because (unless you use EXECUTE) it pre-plans all its statements, so the condition on a.type is not constant at planning time. However, if you PREPARE a statement like prepare v as select * from vale_any a where a.type = $1 and a.cod = $2 and then run it with EXPLAIN ANALYZE EXECUTE v ('P', 2) you will see that although the plan includes the parts of the view that don't get used they are all marked '(never executed)' by EXPLAIN ANALYZE, because the executor had enough information to work out they could never return any rows. Skipping those parts of the plan at execute time does have a small cost--for small tables you will see the total query time go up a little for a prepared statement--but nothing like the cost of scanning a large table. I would expect it's about the same as the cost of a PL/pgSQL IF/THEN/ELSE. It's worth noting at this point that if you know the rows of a UNION will be distinct it's worth making it a UNION ALL, since otherwise Pg has to add a sort-and-uniq step which can be expensive. Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Selecting outside the current DB
Does postgresql allow (or plan to provide) a select outside the current database? Eg. (In Sybase parlance) select * from otherdb..table Thanks, Ben -- _ Ben Stringer[EMAIL PROTECTED] -
[SQL] Functions, transactions and RETURN
After reading up on Postgres documentation, it seems that transactions and savepoints are not available to functions, and savepoints are implemented via BEGIN.. EXCEPTION.. END blocks. I have a function returning an int4 with the following proposed structure: -- BEGIN -- Start the transaction, lock tables SAVEPOINT start; LOCK TABLE backend.table IN ACCESS EXCLUSIVE MODE; LOCK TABLE backend.table2 IN SHARE MODE; -- Check data in another table. IF NOT tt_check_table2(var1) THEN ROLLBACK TO SAVEPOINT start; RETURN -1; -- E_NO_ACCESS END IF; -- Check data in this table. IF tt_check_table(var2) THEN ROLLBACK TO SAVEPOINT start; RETURN -2000; -- E_DUP_COURSE END IF; -- -- Insert the row BEGIN INSERT INTO backend.table (foo, bar, baz) VALUES (1, 2, 3); -- Success RETURN 0; EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT start; RETURN -32767; -- E_UNKNOWN END; ROLLBACK TO SAVEPOINT start; END; -- I can't see how to implement both savepoints and returning distinct values with the BEGIN.. RAISE EXCEPTION.. EXCEPTION.. END method proposed in previous mailing list posts. Are there any suggestions on how to implement this? Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Functions, transactions and RETURN
Hate to reply to my own posts, but I thought the solution I've come across may help others with problems implementing savepoints and transactions in functions. This function implements rollbacks whilst still returning a valid row instead of an exception. A temporary variable is used to get around the ugly lack of SAVEPOINTs. -- CREATE OR REPLACE FUNCTION tr_addcoursearea(employeeno, coursearea) RETURNS int4 AS $BODY$DECLARE transid int4; errcode int4; BEGIN -- Setup default return code. This is used if we hit an -- exception that we didn't throw. SELECT -32767 into errcode; -- E_UNKNOWN LOCK TABLE backend.courseareas IN ACCESS EXCLUSIVE MODE; -- Access to administrators only IF NOT tt_user_access(actor, 'a') THEN SELECT -1 into errcode; -- Return E_NO_ACCESS RAISE EXCEPTION 'User % does not have access.', actor; END IF; -- Check if there are any active course areas with -- the given name. We do not allow duplicate names.. -- confusion may abound. IF tt_coursearea_name_active(area) THEN SELECT -2001 INTO errcode; -- E_DUP_COURSEAREA RAISE EXCEPTION 'Course area "%" already exists.', area; END IF; -- Grab a transaction ID SELECT tt_acquire_transaction(actor, 'tr_addcourse') INTO transid; IF transid < 0 THEN SELECT transid into errcode; -- Return the error code. RAISE EXCEPTION 'Could not acquire transaction.'; END IF; -- Insert the row INSERT INTO backend.courseareas (transactionid, active, caname) VALUES (transid, TRUE, area); RETURN 0; -- SUCCESS EXCEPTION WHEN RAISE_EXCEPTION THEN RETURN errcode; WHEN OTHERS THEN RETURN -32767; -- E_UNKNOWN END;$BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; ------ Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(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] how to do 'deep queries'?
> Is there supported syntax to do 'deep' queries? That is where > A relates to B relates to C, returning fields from each table? > > This doesn't seem to work. Is there a google-able term for > this sort of query? > > select >foo.aaa, >bar.bbb, >baz.ccc > > from >foo,bar,baz > > where >foo.bar_id = bar.id > and >bar.baz_id = baz.id This works for me.. SELECT table1.state, table2.coursename, table3.firstname FROM backend.enrolments table1, backend.courses table2, backend.users table3 WHERE table1.user = table3.employeeno AND table1.course = table2.courseid; What errors are you getting? Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA Tel: +61 3 9541-7002 Fax: +61 3 9541-7700 mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] changing a column's position in table, how do you do that
Ferindo, > Is there a way to change the position attribute of a column > in a table? AFAIK, there's no way to change this easily. The best way to do it would be as follows: BEGIN WORK; LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE; ALTER TABLE mytable ADD COLUMN col_to_move_2 coltype; UPDATE mytable SET col_to_move_2 = col_to_move; ALTER TABLE mytable DROP COLUMN col_to_move; ALTER TABLE mytable RENAME col_to_move_2 TO col_to_move; COMMIT WORK; Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Sending function parametars within EXECUTE ''SELECT...
> Is there a way to assing variable a value returned from query that > accesses the temporary table in a way you explained above? > > For instance, I can do: > > myValue := col2 FROM tmpTbl WHERE someValue = somethingElse... If I'm parsing this correctly, all you need do is: SELECT col2 INTO myValue FROM ....... Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Scripting GRANT on functions
Is there any easy way to script granting privileges to a number of functions? I've got as far as the following code before realising that I'll need to pass in the arguments, and the arguments are stored as OIDs in pg_proc. Is there any easy way, such as GRANT FUNCTION OID 12345? ---CODE--- DECLARE curs REFCURSOR; funcname VARCHAR; BEGIN OPEN foo FOR SELECT proname FROM pg_proc WHERE proname LIKE 'tr\\_%' OR proname LIKE 'tt\\_%' OR proname LIKE 'v\\_%' OR proname LIKE 'vui\\_%'; FETCH curs INTO funcname; WHILE FOUND LOOP FETCH curs INTO funcname; EXECUTE 'GRANT EXECUTE ON FUNCTION ' || funcname || ' TO myuser'; END LOOP; CLOSE curs; END; ---END CODE--- Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SEVEN cross joins?!?!?
> Splitting locations into subsets (like 2,2,3) doesn't work > because it is possible that low values in one location can be offset by > high values in another location, and still result in an excellent combo. > > The good news is these suggestions got me thinking outside > the box. I think I can program a modified brute-force that bypasses > large numbers of combos early. It might still be too large/slow, so I'd be > interested in finding more info about these "smarter algorithms" in option Could you define an view with a calculated field, say, 2 * a1 + 6 * a2 + 3 * a3, and then use this aggregate to score the individual rows? I haven't looked at the exact nature of the problem, but if you're multiplying a table by itself, it may be worth redefining the problem in terms of a simple ranking algorithm, define a column to calculate this, then sort by that column. Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA Tel: +61 3 9541-7002 Fax: +61 3 9541-7700 mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Update timestamp on update
> The problem is that we treat the PL languages as add-ons and therefore > the documentation of the "core" system shouldn't rely on them ... but > that leaves us presenting C-code triggers as the only examples in > chapter 35. There is a paragraph in there suggesting you go look at > the PL languages first, but obviously it's not getting the job done. > > Anybody have a better idea? Include a sample trigger for each PL that is shipped with the standard distribution, along with comments to the effect that "it is suggested that the documentation for $PL is read before creating triggers"? It may also be worth including a comment that explicitly states that all(?) PLs are supported in triggers. Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about functions
Mike, > I am trying to run this function but the return is not > correct. If I run the select statement from the psql command > line it works. My guess is that the WHERE clause could be > causing the problem. Then again, it may be how I am using > the FOR loop. The ides column is of type TEXT. > > CREATE OR REPLACE FUNCTION sp_description_search(varchar) > RETURNS varchar AS $$ -- <== Problem is here > DECLARE > myrec record; > BEGIN > FOR myrec IN SELECT * FROM tblStuff WHERE ides LIKE '%$1%' LOOP > RETURN NEXT myrec;-- <== Problem is here > END LOOP; > RETURN; > END; > $$ LANGUAGE 'plpgsql'; You need to declare this as a set-returning function if you're using RETURN NEXT. Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem while using start transaction ans commit;
Sri,
> I have a small problem in using nested transactions while
> working on Postgres 8.0.
This is a known problem with Postgres 8.0 - there is no support for
nested transactions (which occurs when calling functions). Your best bet
would be to raise an exception within B or C - this will cause a
rollback to wherever the exception is caught. If you surround the calls
to B and C in a block to catch the exception, this will provide
transaction-like semantics.
An example:
--
CREATE OR REPLACE FUNCTION tr_addcourse(employeeno, int4, coursename,
float4, text, timestamptz, int4)
RETURNS int4 AS
$BODY$-- Use case: 10.2.9: Add a course
DECLARE
transid int4;
cid int4;
errcode int4;
BEGIN
-- Setup default return code. This is used if we hit an
-- exception that we didn't throw.
SELECT -32767 into errcode; -- E_UNKNOWN
-- Start the transaction, lock tables
LOCK TABLE backend.courses IN ACCESS EXCLUSIVE MODE;
LOCK TABLE backend.courseareas IN SHARE MODE;
-- Access to administrators only
IF NOT tt_user_access(actor, 'a') THEN
SELECT -1 into errcode; -- Return E_NO_ACCESS
RAISE EXCEPTION 'User % does not have access.', actor;
END IF;
-- Check for a duplicate course name.
IF tt_course_name_active(cname) THEN
SELECT -2000 INTO errcode; -- E_DUP_COURSE
RAISE EXCEPTION 'Course "%" already exists.', cname;
END IF;
-- Check for course area status
SELECT tt_coursearea_status(carea) INTO errcode;
IF NOT errcode = 0 THEN -- NOT errcode = SUCCESS
RAISE EXCEPTION 'Error finding active course area %', carea;
END IF;
-- Grab a transaction ID
SELECT tt_acquire_transaction(actor, 'tr_addcourse') INTO transid;
IF transid < 0 THEN
SELECT transid into errcode; -- Return the error code.
RAISE EXCEPTION 'Could not acquire transaction.';
END IF;
-- Get the next course ID
SELECT nextval('backend.courses_courseid_seq') INTO cid;
-- Insert the row
INSERT INTO backend.courses
(transactionid, courseid, coursearea, coursename, active, duration,
description, contentdate, valid_months)
VALUES (transid, cid, carea, cname, TRUE, dur, desc, cdate,
valid_mths);
-- Success
RETURN cid;
EXCEPTION
WHEN RAISE_EXCEPTION THEN
RETURN errcode;
WHEN OTHERS THEN
RETURN -32767; -- E_UNKNOWN
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
--
In this code, whenever an exception is raised, the system will rollback
to the start of the block (BEGIN).
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:[EMAIL PROTECTED]
http://www.bosch.com.au/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] automatic update or insert
> Currently i have implemented this as a stored procedure in the plpgsql > language. This means that in my stored procedure i first do a > select to > find out if the row exists or not, then i do a insert or update > depending if the row existed. > > Unfortunately, stored procedures seems awfully slow. And i need the > application to go faster. Do you have indexes on the columns being looked up? Make sure you create the index then ANALYZE. > One solution could be to implement the stored procedure in my program > instead. I think that this will be atleast 50% faster than my stored > procedure, so that would be ok. > > However, this has made me thinking. Couldn't this be done directly in > SQL? You could probably implement this as a trigger on the table for INSERT. Have a squiz through the documentation on triggers. Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] how to create rule as on delete
> If I add the DO INSTEAD,the record can be inserted > into 'maytable_log' and also still remain in the table > 'maytable'. Which is exactly not exist at all. It just > show it as im using the DO INSTEAD. > > My problem is, how to insert the deleted record into > table 'mytable_log' without showing it in table > 'maytable'. I really need the solution..please Add a DELETE clause on the table 'maytable' as part of the DO INSTEAD, after the INSERT INTO. Or, IIRC, you could INSERT INTO (without the DO INSTEAD) with the variable NEW - the NEW variable will store the row being deleted. Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA Tel: +61 3 9541-7002 Fax: +61 3 9541-7700 mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
