[SQL] Copying a rowtype variable.
Hello, Just doing a bit of PL/PGSQL so my first question is: 1) should PL/PGSQL questions be posted to the general mailing list, the sql mailing list or both? My second question is: 2) I am looking for an elegant way of copying a rowtype variable: eg. DECLARE current_row orf%ROWTYPE; previous_row orf%ROWTYPE; BEGIN LOOP -- use cursors or FOR SELECT to get values into current_row -- now try this: previous_row = current_row; END LOOP; END; Now, as I anticipated, this fails because a rowtype variable is a composite variable. One working alternative is to do: previous_row.pid = current_row.pid; previous_row.start = current_row.start; -- and so on so forth for all current_row variables But this is inconvenient of the row has many fields and impossible if you want a flexible function which can accomodate rowtypes for which the fields are not known "a priori". Any clever workarounds? Cheers, Stuart. PS. I started using PGSQL five years ago... then I had to leave it alone for a while... I can't believe how much it's grown and developed. AMAZING, congrats to all developers. -- Stuart C. G. Rison Department of Pathology and Infectious Diseases Royal Veterinary College London. United Kingdom. "That's bioinformatics... and we don't want to be doing bioinformatics, we want to do real science.", Sydney Brenner, UCL Pfizer Lecture, 8 May 2001 -- ---(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] Copying a rowtype variable.
>> 2) I am looking for an elegant way of copying a rowtype variable: >> >> eg. >> >> DECLARE >> current_row orf%ROWTYPE; >> previous_row orf%ROWTYPE; >> BEGIN >> >> LOOP >> -- use cursors or FOR SELECT to get values into current_row >> -- now try this: >> >> previous_row = current_row; >> END LOOP; >> END; >> >> Now, as I anticipated, this fails because a rowtype variable is a >> composite >> variable. One working alternative is to do: >> > > I haven't tried this. One thing I notice above is that you're using > the equality operator "=" instead of the assignment operator ":=" . > Usually Postgres lets you slack on this, but it would be worth trying > to see whether that has an effect on the problem. > Fair point. But "previous_row := current_row" doesn't work either. > Another thing to try is, instead of a simple variable assignment > > SELECT current_row INTO previous_row; > > ... and see if that works. Well, I had high hopes for that one... but it didn't work either! > I'll tinker later today; there has to be a way to do it. I'd definitely appreciate further suggestions, but thanks all the same for you help. I have a feeling that you might have to write a PL function to perform the operation... but I haven't really thought about it! Stuart. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Copying a rowtype variable.
Josh has submitted an appropriate report to pgsql-bugs... so perhaps it will happen! Stuart. > -Original Message- > From: Jean-Luc Lachance [mailto:jllachan@;nsd.ca] > Sent: 07 November 2002 16:29 > To: Rison, Stuart > Cc: ''[EMAIL PROTECTED]' '; 'Josh Berkus ' > Subject: Re: [SQL] Copying a rowtype variable. > > I would personnaly like this feature (assigning a composite from another > similar composite) to be added to PLPGSQL. Another nice feature would be > to able to insert a composite into a table without have to name all > atributes. > > Just my $.02 > > "Rison, Stuart" wrote: > > >> 2) I am looking for an elegant way of copying a rowtype variable: > >> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Two TIMESTAMPs in one pl/sql function
Hi, I'm trying to time a pl/sql function using a rougn and ready method, basically: print a TIMESTAMP at the begining of the function, print a TIMESTAMP at the end of the function. So...: CREATE OR REPLACE FUNCTION timer() RETURNS INTEGER AS ' DECLARE timer1 TIMESTAMP; timer2 TIMESTAMP; num_operators INTEGER; BEGIN timer1 := ''now''; -- As suggested in 23.4 of programmer guide RAISE NOTICE ''Start: %'', timer1; /* Some function which takes time. Here, a select from a pg catalogue */ SELECT INTO num_operators COUNT(*) FROM pg_operator; timer2 := ''now''; RAISE NOTICE ''End: %'', timer2; RETURN(num_operators); END;' LANGUAGE 'plpgsql'; Gives me: testdb2=# select timer(); NOTICE: Start: 2002-11-26 13:40:14.116605+00 NOTICE: End: 2002-11-26 13:40:14.116605+00 timer --- 623 (1 row) I've tried all sorts of variations but I hit one of two problems; either the TIMESTAMP is fixed to the function compile time or, as above, the timer1 and timer2 TIMESTAMPs are always identical. Any help/pointers/suggestions appreciate... well of course a working solution would be the best ;) Cheers, Stuart. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] [SQL/BUGS] (possible) inconsistent behaviour of timestamp?
Here's an "interesting" timestamp related postgreSQL quirk:
testdb2=# select "timestamp"('now');
timestamp
2002-11-26 13:47:12.454157
(1 row)
testdb2=# select 'now'::timestamp;
timestamptz
--
2002-11-26 13:47:34.88358+00
(1 row)
testdb2=# select timestamp 'now';
timestamptz
---
2002-11-26 13:47:47.701731+00
(1 row)
The first SELECT returns a 'timestamp', but the next two return a
'timestamptz' (presumably with timezone); is this inconsitent behaviour?
Cheers,
Stuart.
Royal Veterinary College
London, UK
---(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
