Re: [SQL] Getting multiple rows in plpgsql function
NOTE: This is a feature in 7.3 it was either added or fixed, so you will not be able to do this unless you are using version 7.3. Remember to backup with pg_dumpall before you upgrade. This is a sample sent to me earlier this week, that iterates an integer array: Cut Here CREATE TYPE group_view AS (grosysid int4, groname name, usesysid int4, usename name); CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS ' DECLARE rec record; groview record; low int; high int; BEGIN FOR rec IN SELECT grosysid FROM pg_group LOOP SELECT INTO low replace(split_part(array_dims(grolist),'':'',1),''['',)::int FROM pg_group WHERE grosysid = rec.grosysid; IF low IS NULL THEN low := 1; high := 1; ELSE SELECT INTO high replace(split_part(array_dims(grolist),'':'',2),'']'',)::int FROM pg_group WHERE grosysid = rec.grosysid; IF high IS NULL THEN high := 1; END IF; END IF; FOR i IN low..high LOOP SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i] WHERE grosysid = rec.grosysid; RETURN NEXT groview; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql' WITH ( iscachable, isstrict ); CREATE VIEW groupview AS SELECT * FROM expand_groups(); Cut Here One of the tricks is that you apparently need to use the CREATE TYPE commands to define the returned result. The veiw at the end just makes queries look like a table is being queried rather than a function. I hope this helps. Roberto Mello wrote: On Fri, Jan 24, 2003 at 11:39:07AM -0800, David Durst wrote: I am wondering how you would handle a select that returns multiple rows in a plpgsql function? In other words lets say I wanted to iterate through the results in the function. There are examples in the PL/pgSQL documentation that show you how to do it. -Roberto ---(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] Function for adding Money type
> David, > >> Are there functions for adding and subtracting this type from itself? >> Or is there a simple way to do it? > > The MONEY type is depreciated, and should have been removed from the > Postgres source but was missed as an oversight. Use NUMERIC instead. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco Already done, I found it in another doc. Thanks though ---(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] Function for adding Money type
David, > Are there functions for adding and subtracting this type from itself? > Or is there a simple way to do it? The MONEY type is depreciated, and should have been removed from the Postgres source but was missed as an oversight. Use NUMERIC instead. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Function for adding Money type
Are there functions for adding and subtracting this type from itself? Or is there a simple way to do it? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting multiple rows in plpgsql function
On Fri, Jan 24, 2003 at 11:39:07AM -0800, David Durst wrote: > I am wondering how you would handle a select that returns multiple rows > in a plpgsql function? > > In other words lets say I wanted to iterate through the results in > the function. There are examples in the PL/pgSQL documentation that show you how to do it. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + And God said: E = ½mv² - Ze²/r, and there was light. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Scheduling Events?
here is a possible NON-Cron solution that a friend of mine came up w/ 1) Create a table w/ scheduled events and Account Ids attached to them. 2) Create a table w/ temporal event execution timestamps. 3) On journal entry check to see if there any schedule events for the Account 4) Check timestamp table for last execution If Last execution is out of range force execution Else continue as normal This is passive but it should allow for data integrity w/ out the need of a external system. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Scheduling Events?
Hi I would agree that cron is probably the best solution. You could have cron perform a query that has a trigger and performs all the tasks you need done. As well you could create a trigger on other queries that would perform the other things as well, but make sure it isn't a heavily used query but instead a query that is run hourly or daily. As a backup for cron you could manualy or using "anacron" or somthing similar run the query cron should run on a regular basis, but you should make sure your trigger keeps an entry in your database letting the other queries know when the update is started and when it has finished. Using this check ensures you don't get "overlapping" updates, and can also give you a clue to how much time the updates are taking and possibly alert you to a "hung" update. Guy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL to list databases?
Hi
To make it easier to do this in SQL you can create a view like this :
CREATE VIEW db_list AS
SELECT d.datname as "Name",
u.usename as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
ORDER BY 1;
Note: the "select" statement comes from the post I am replying from.
Then all you have to do is :
select * from db_list;
For example this is my output :
foobar=# select * from db_list;
Name| Owner | Encoding
---+---+---
foobar| turk | SQL_ASCII
template0 | pgsql | SQL_ASCII
template1 | pgsql | SQL_ASCII
(3 rows)
Or :
foobar=# select "Name","Owner" from db_list where "Owner" != 'pgsql';
Name | Owner
+---
foobar | turk
(1 row)
Using psql -E {database} interactivly
Or
psql -E -c "\{command}" {database}
Example:
user@host:~$ psql -E -c "\dt" template1
* QUERY **
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**
You can collect the SQL for other helpful commands and build views like above,
then you can query the view for more specific information.
I hope this is helpful.
Guy
PS: If you create these "views" in template1 before you create your other
databases, these views will be included in new databases automaticaly.
Larry Rosenman wrote:
--On Thursday, January 23, 2003 12:56:50 -0600 Ben Siders
<[EMAIL PROTECTED]> wrote:
Is there a query that will return all the databases available, similar to
what psql -l does?
$ psql -E -l
* QUERY **
SELECT d.datname as "Name",
u.usename as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
ORDER BY 1;
**
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Scheduling Events?
Or if you are so paranoid about the stability of crond, you can probably do a check to see whether crond is up when you update the database. If crond is up then update else mail root the error reject the update end This is going to affect the performance dramatically though. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Achilleus Mantzios" <[EMAIL PROTECTED]> Cc: "David Durst" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, January 24, 2003 10:32 AM Subject: Re: [SQL] Scheduling Events? > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > On Fri, 24 Jan 2003, David Durst wrote: > >> Here is the basic problem w/ using CRON in an accounting situation. > >> > >> I can't be sure that cron will always be up when the DB is up, > >> so lets say crond goes down for some random reason (User, System error, > >> Etc..) > > > I you cannot depend on your system to run crond > > then you should not depend on it to run postgresql either. > > Indeed. Cron is one of the oldest and most basic Unix daemons. > I find it really, really, really hard to believe that any substitute > code that anyone might come up with is going to be more reliable than > cron. > > If it makes you feel better, you could institute some cross-checking. > For example, have the cron-launched task update a timestamp in some > database table whenever it finishes. Then make your user applications > check that timestamp when they start up (or every so often) and complain > if it's not within the range (now - expected cron frequency, now). > That doesn't fix the problem, but at least makes some humans aware of it. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CAST from VARCHAR to INT
daq <[EMAIL PROTECTED]> writes: > Make your life easier! :) You must write a function like > this: > create function "int4"(character varying) returns int4 as ' >DECLARE > input alias for $1; >BEGIN > return (input::text::int4); >END; > ' language 'plpgsql'; > When you try the cast varchar_field::integer or varchar_field::int4 Postgres call > the function named int4 and takes varchar type parameter. Note that as of 7.3 you need to issue a CREATE CAST command; the name of the function is not what drives this anymore. (Though following the old naming convention that function name == return type still seems like a good idea.) 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] Scheduling Events?
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > On Fri, 24 Jan 2003, David Durst wrote: >> Here is the basic problem w/ using CRON in an accounting situation. >> >> I can't be sure that cron will always be up when the DB is up, >> so lets say crond goes down for some random reason (User, System error, >> Etc..) > I you cannot depend on your system to run crond > then you should not depend on it to run postgresql either. Indeed. Cron is one of the oldest and most basic Unix daemons. I find it really, really, really hard to believe that any substitute code that anyone might come up with is going to be more reliable than cron. If it makes you feel better, you could institute some cross-checking. For example, have the cron-launched task update a timestamp in some database table whenever it finishes. Then make your user applications check that timestamp when they start up (or every so often) and complain if it's not within the range (now - expected cron frequency, now). That doesn't fix the problem, but at least makes some humans aware of it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] quastions about primary key
On Fri, 24 Jan 2003, jack wrote: > Is that possible to have a two columns primary key on a table with null > value on second column? No, because primary key implies not null on all columns involved (technically I think it's that a non-deferrable primary key implies not null on all columns involved, but we don't support deferrable ones) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Scheduling Events?
On Fri, Jan 24, 2003 at 00:45:38 -0800, David Durst <[EMAIL PROTECTED]> wrote: > > I can't be sure that cron will always be up when the DB is up, > so lets say crond goes down for some random reason (User, System error, > Etc..) One option would be to run the cron job fairly often and have it check if there are any tasks that need to be done. If there are it does them in a transaction which also makes some update that indicates that the task has been done. The other option would be that the process(es) that use the monthly updates, check to see that they have been done as part of their transaction and if not do the update first before proceeding. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] quastions about primary key
jack wrote: Is that possible to have a two columns primary key on a table with null value on second column? Jack Probably not, because (1,2,null,null) is unique for postresql. Watch discussion on mailing list about unique indexes (on which primary key is based) several days ago. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CAST from VARCHAR to INT
Hello! Like others said you can't cast varchar to int directly. Make your life easier! :) You must write a function like this: create function "int4"(character varying) returns int4 as ' DECLARE input alias for $1; BEGIN return (input::text::int4); END; ' language 'plpgsql'; When you try the cast varchar_field::integer or varchar_field::int4 Postgres call the function named int4 and takes varchar type parameter. DAQ ---(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] Scheduling Events?
On Fri, 24 Jan 2003, David Durst wrote: > > On Thu, 23 Jan 2003, David Durst wrote: > > > > Here is the basic problem w/ using CRON in an accounting situation. > > I can't be sure that cron will always be up when the DB is up, > so lets say crond goes down for some random reason (User, System error, > Etc..) > I you cannot depend on your system to run crond then you should not depend on it to run postgresql either. > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] calling function from rule
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 7:01 PM > To: Tambet Matiisen > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] calling function from rule > > > "Tambet Matiisen" <[EMAIL PROTECTED]> writes: > >> Try 7.3, we changed the rules about returned records count. > > > I have 7.3. When rule and action are the same, everything > works fine. > > Doing an insert in update rule and opposite are OK too. Problem is, > > when I do select in insert/update/delete rule. Then the result of > > select is returned instead of command status, even if the select > > is done in non-instead rule and there is unconditional instead rule. > > Oh, I think your complaint is really about the fact that psql doesn't > print the command status if it got any tuples (or even just a tuple > descriptor) in the result series. AFAICT the information returned by > the backend is sensible in this situation: the "UPDATE 1" > status message > *is* returned and is available from PQcmdStatus. psql is > just choosing > not to print it. I'm not sure that that's wrong, though. > Thanks, I only tested it with psql and got worried. Tambet ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Scheduling Events?
> On Thu, 23 Jan 2003, David Durst wrote: > >> Is there anyway to schedule DB Events based on time? > > Yes! cron > >> So lets say I had a table w/ depreciation schedules in it, >> I would like the DB to apply the formula and make the entries on the >> END of every month. > On Thu, 23 Jan 2003, David Durst wrote: > >> Is there anyway to schedule DB Events based on time? > > Yes! cron > >> So lets say I had a table w/ depreciation schedules in it, >> I would like the DB to apply the formula and make the entries on the >> END of every month. Here is the basic problem w/ using CRON in an accounting situation. I can't be sure that cron will always be up when the DB is up, so lets say crond goes down for some random reason (User, System error, Etc..) And outside adjustment is made to lets say the equipment account and that adjustment was made on the value of the equipment, BUT it hadn't been depreciated because crond went down and no one notice. Now I have a HUGE issue! So I have to be sure that all entries/adjustments are made accurately in the time frame they were meant to happen in. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Race condition w/ FIFO Queue reappears!
Tom's suggested solution worked GREAT in 7.2.x ... I tried taking the plunge into 7.3.1 tonight. In 7.3.1, when my FIFO queue program goes to grab a row, TWO processes grab the same row, almost without fail. I even changed my locking statement to the dreaded LOCK TABLE fifo IN ACCESS EXCLUSIVE MODE; it still exhibits the same behavior. I've tried variations on the theme, but I can't seem to figure it out. I'm stumped! The postgresql configuration is as identical (IMO) as I could possibly make it considering the changes from 7.2 to 7.3. I can't imagine a config option would control something so basic. I can't find any reference to it in the 7.3 docs, and my tired eyes did not pick any fixes remotely pertaining to this type of locking problem in the HISTORY file. I'm (sadly) switching back to 7.2 until we can figure this out. CG >Chris Gamache <[EMAIL PROTECTED]> writes: >> I have a program that claims a row for itself >> my $processid = $$; >> my $sql_update = <> UPDATE fifo >> set status=$processid >> WHERE id = (SELECT min(id) FROM fifo WHERE status=0); >> EOS >> The problem occurrs when two of the processes grab the exact same row at the >> exact same instant. > >Probably the best fix is to do it this way: > > BEGIN; > LOCK TABLE fifo IN EXCLUSIVE MODE; > UPDATE ... as above ... > COMMIT; > >The exclusive lock will ensure that only one process claims a row >at a time (while not preventing concurrent SELECTs from the table). >This way you don't need to worry about retrying. > > regards, tom lane __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql: debugging
--- Oliver Vecernik <[EMAIL PROTECTED]> wrote: > Hi, > > Searching Google I found a thread in July 2001 > concerning the facilities > for debugging plpgsql functions. The actual answer > was: it should be > improved. > > What is the best way to debug a plpgsql function? > > Oliver This may not be the best way since its a bit crude. Try using RAISE NOTICE every now then to monitor the values of variables in the screen and record it in log file. Example : RAISE NOTICE ''Initial value of variable = %'',v_variable; /* Do some computation ... */ RAISE NOTICE ''Value of variable after operation = %'',v_variable; hope that helps, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
