[SQL] PL/pgSQL multidimension (matrix) array in function
I found how to use, inside a PL/pgSQL function, a two-dimensions array (matrix). There is a limitation: the number of the 'columns' of the matrix is fixed at declaration time (in DECLARE section) and you need to manually initialize all the elements in the first 'row' of the matrix. The number of rows is unlimited and can be sized at runtime. Here is the code that can help you. If someone know how to manage an NxN array without limitations, please replay to this thread. I'm using PostgreSQL 7.4.1 on RH7.3 Cheers Sergio CREATE OR REPLACE FUNCTION "testarray" (integer) RETURNS SETOF integer AS' DECLARE n alias for $1; -- number of rows is passed as argument i INTEGER; j integer; k INTEGER := 3;-- matrix columns number b integer[] := array[0,0,0];-- need it to initialize the matrix!! a integer[][] := array[[0,0,0]]; -- need it to initialize the matrix!! begin for i in 1..n loop -- the i loop can start obviously from 2 (the first row is already present...) but for our purpose here we use 1 a := array_cat(a,b); for j in 1..k loop a[i][j] := i*j; end loop; end loop; for i in 1..n loop return next null; return next i;-- need it to format in some way the output :) return next null; for j in 1..k loop return next a[i][j]; end loop; end loop; return; end 'LANGUAGE 'plpgsql'; here is the output: => select * from testarray(8); testarray --- 1 1 2 3 2 2 4 6 3 3 6 9 4 4 8 12 5 5 10 15 6 6 12 18 7 7 14 21 8 8 16 24 (48 rows) -- - Sergio Fantinel EGEE Project - INFN - Lab. Naz. di Legnaro phone: +39 049 8068 489 viale dell'Università n. 2, 35020 Legnaro (PD) ITALY [EMAIL PROTECTED] - ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Implicit Transactions
We are porting from Informix to PostgreSQL 7.4.5 and have noticed the following behavior. If we try to OPEN a CURSOR for an invalid SELECT statement in ECPG, we get an error, as expected. However, if we then attempt to OPEN another CURSOR for a valid statement, we get an error that says we are in a failed transaction. At that point, no statement will succeed. The only way we have found to get out of this state is to ROLLBACK explicitly. We are not using explicit transactions. My understanding is that PG should be rolling back failed statements when we are not in an explicit transaction. Do we have an incorrect setting, a misunderstanding of how it is supposed to work, or a bug? Could anyone enlighten me? Thanks, Chip ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] 1-byte integers
how can i specify an integer to be one byte byte or even 4 bits long? int1, int(1), tinyint are nowhere to be seen. smallest i can find is smallint that is 2 bytes. in a table i will have a few columns with values between 0 and 15, so optimally i am looking for some kind of unsigned 4-bits, or (un)signed integer (1-byte). i can live with smallint for a while but estimate a few millions of rows in the table to be a reality soon, in such cases every byte matters. is there some stig ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to check postgres running or not ?
Hello Sir, I want to know how to check whether postgres database is running or not ? when I give command like ./postmaster -i &, whether all databases in that postgres will run or any one [default] ? If any one, then how to detect that database ? Thanks and regards, Sandeep. - Disclaimer: The contents of this message are confidential and intended to the addressee at the specified e-mail address only. Its contents may not be copied or disclosed to anyone other than the intended recipient. If this e-mail is received in error, please contact Vertex Software Pvt. Ltd immediately on +91 20 4041500 with details of the sender and addressee and delete the e-mail. Vertex Software Pvt. Ltd accepts no responsibility in the event that the onward transmission, opening or use of this message and/or any attachments adversely affects the recipient's systems or data. It is the recipient's responsibility to carry out such virus and other checks as the recipient considers appropriate. - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Sequence of SQL command execution involving triggers and stored procedures.
Title: Message I am observing the following: Say there is a stored procedure i.e FUNCTION sp_foo(...) which has say, SQL commands (any of SELECT,INSERT,UPDATE,DELETE) in its body, say S1 followed by S2 then S3 Furthermore, execution of statement S2 results in trigger functions getting invoked (that contain yet other SQL statements). By the time, the trigger functions execute (as a result of S2), S3 has already executed. Note that sp_foo() was invoked via a SELECT i.e SELECT sp_foo(...). To work around this "behaviour", I wrapped sp_foo() within another FUNCTION say sp_foo_wrapper(...) and in the body of sp_foo_wrapper() I did this - 1. INSERT in a scratch table // akin to setting a flag 2. Invoke sp_foo(). 3. DELETE from the scratch table // akin to resetting the flag Even then, by the time the triggers fired as a result of S2 (in the body of sp_foo), the DELETE (in step 3 above) had executed. Questions: 1. Did I miss something very basic? 2. Is this behavior random i.e the manifestation pertians to the particular SQL commands in my code? 3. How does one achieve, the chronolgy that I want i.e I want S3 AND/OR step 3 to occur AFTER the triggers (as a result of S2) to fire? Thanx in advance, Omkar Rath Software Engr. VTG Cisco Systems Inc.
Re: [SQL] 1-byte integers
stig erikson <[EMAIL PROTECTED]> writes: > how can i specify an integer to be one byte byte or even 4 bits long? > int1, int(1), tinyint are nowhere to be seen. > smallest i can find is smallint that is 2 bytes. There's a type called "char" (the double quotes are needed). It's used by postgres system catalogues but it's available for users too. It's a little quirky since it has operators that treat it as a 1 byte text data type and other operators that treat it as an integer data type. But that doesn't normally lead to any problems, just strange symptoms when your code has a bug. Other than that there are things like bit(4) which has a cast to and from integer. But they won't save you any storage space. If you have multiple columns like this and want to define a new type that aggregates them all for storage but lets you access them individually that could be useful. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Sequence of SQL command execution involving triggers and stored procedures.
"Omkar Rath" <[EMAIL PROTECTED]> writes: > Say there is a stored procedure i.e FUNCTION sp_foo(...) which has say, > SQL commands (any of SELECT,INSERT,UPDATE,DELETE) in its body, say S1 > followed by S2 then S3 Furthermore, execution of statement S2 > results in trigger functions getting invoked (that contain yet other SQL > statements). > > By the time, the trigger functions execute (as a result of S2), S3 has > already executed. In existing PG releases, AFTER triggers are always fired at the completion of the whole interactive command (ie, the outer SELECT that called sp_foo). There are changes in 8.0 to fire them at the end of the specific query that triggered them, which I believe will do what you want. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to check postgres running or not ?
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] ("Sandeep
Gaikwad") transmitted:
> Hello Sir,
> I want to know how to check whether postgres database
> is running or not ? when I give command like ./postmaster -i &,
> whether all databases in that postgres will run or any one [default] ?
> If any one, then how to detect that database ?
Well, the way I usually check on what databases are running is thus:
[EMAIL PROTECTED]:/tmp/mm5/doc> netstat -an | grep PG
Saturday 13:18:30
unix 2 [ ACC ] STREAM LISTENING 2793
/var/run/postgresql/.s.PGSQL.5432
One could presumably script things further to get more out of that; it
doesn't normally seem worthwhile to do so...
--
output = ("cbbrowne" "@" "ntlug.org")
http://www.ntlug.org/~cbbrowne/postgresql.html
"As long as war is regarded as wicked, it will always have
its fascination. When it is looked upon as vulgar,
it will cease to be popular."
--Oscar Wilde
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to check postgres running or not ?
Sandeep Gaikwad wrote: Hello Sir, I want to know how to check whether postgres database is running or not ? when I give command like ./postmaster -i &, whether all databases in that postgres will run or any one [default] ? If any one, then how to detect that database ? Well, in the $DATADIR you habe postmaster.pid that contains the supposed pid... Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to check postgres running or not ?
Assuming it is unix The command ps xau|grep post If it is running it will produce output like... [EMAIL PROTECTED]:~$ ps xau|grep post postgres 880 0.0 0.2 8580 740 ?SSep07 0:31 /usr/lib/postgresql/bin/postmaster postgres 887 0.0 0.2 9536 612 ?SSep07 0:02 postgres: stats buffer process postgres 889 0.0 0.2 8624 632 ?SSep07 0:05 postgres: stats collector process worik25927 0.0 0.2 1612 540 pts/10 S13:54 0:00 grep post [EMAIL PROTECTED]:~$ If it is not running it will produce output like... [EMAIL PROTECTED]:~$ ps xau|grep post worik26094 0.0 0.2 1608 528 pts/10 S13:55 0:00 grep post Sandeep Gaikwad wrote: Hello Sir, I want to know how to check whether postgres database is running or not ? when I give command like ./postmaster -i &, whether all databases in that postgres will run or any one [default] ? If any one, then how to detect that database ? Thanks and regards, Sandeep. - Disclaimer: The contents of this message are confidential and intended to the addressee at the specified e-mail address only. Its contents may not be copied or disclosed to anyone other than the intended recipient. If this e-mail is received in error, please contact Vertex Software Pvt. Ltd immediately on +91 20 4041500 with details of the sender and addressee and delete the e-mail. Vertex Software Pvt. Ltd accepts no responsibility in the event that the onward transmission, opening or use of this message and/or any attachments adversely affects the recipient's systems or data. It is the recipient's responsibility to carry out such virus and other checks as the recipient considers appropriate. - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
