[SQL] selective updates
sorry for posting a new opening 'thread', but my subscribing to the ml is later than a post with the subject 'APPEND INTO?' dated Thu, 01 Dec 2005 10:32:08 -0500 so i can't make reply to that. follows a copy of that post Mark Fenbers writes: I want to SELECT INTO mytable WHERE (criteria are met), except that I want to APPEND into an existing table the rows that are selected, instead of creating a new table (which SELECT INTO will do). How can this be done? INSERT INTO foo SELECT whatever regards, tom lane and what if we need to be selective when inserting rows let's say we have the following test-db CREATE TABLE table1 ( column1 int, column2 varchar(20), column3 numeric, column4 int ); CREATE TABLE table2 ( column1 int, column2 varchar(20) ); and the population is: test-db=# SELECT * from table1; column1 | column2 | column3 | column4 -+-+-+- 1 | some name | 3.5 | 1 | some other | 4.7 | 2 | some some | 3 | 2 | some else | 3 | 3 | some thing | 8.5 | 3 | some thing else | 8.3 | (6 righe) test-db=# test-db=# SELECT * from table2; column1 | column2 -+ 1 | some info 1 | some info1 1 | some info2 2 | some info3 2 | some info4 2 | some info5 (6 righe) test-db=# now we need to update the table1.column4 to store the values from table2.column1 when table1.column2 table2.column2 match a given citeria i think we would need a transaction block andor SQL functions, but i didn't find myself a solution yet. Gianluca Riccardi p.s. i'm using PostgreSQL 7.4.7 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] RETURN SET OF DATA WITH CURSOR
Hi !
I am making one plpgsql function and I need to return of setof data
using a cursor.
The problem is that my function is returning only the first row and
column of the query. This query have more than 80 columns and 1.000
rows. Enyone have any tip to give me?
Here the fuction...
CREATE OR REPLACE FUNCTION rel_faturamento("varchar","varchar")
RETURNS refcursor AS
$BODY$
DECLARE
data_inicial ALIAS FOR $1;
data_final ALIAS FOR $2;
ref refcursor;
fat_cursor CURSOR FOR
SELECT * FROM SF2010 SF2
INNER JOIN SD2010 SD2 ON (SD2.D2_DOC = SF2.F2_DOC)
INNER JOIN SB1010 SB1 ON (SB1.B1_COD = SD2.D2_COD)
INNER JOIN SA1010 SA1 ON (SF2.F2_CLIENTE = SA1.A1_COD)
WHERE F2_EMISSAO >= data_inicial AND F2_EMISSAO <=
data_final
AND SF2.D_E_L_E_T_<> '*' AND sd2.d2_tes IN (SELECT f4_codigo FROM
sf4010 WHERE d_e_l_e_t_ <> '*' AND f4_duplic = 'S' AND f4_codigo >= '500')
ORDER BY SF2.F2_EMISSAO, SF2.F2_DOC, SF2.F2_HORA;
BEGIN
OPEN fat_cursor;
LOOP
FETCH fat_cursor INTO ref;
RETURN ref;
END LOOP;
CLOSE fat_cursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
When I execute it, I only receive the return below:
SELECT rel_faturamento('20051201','20051231');
rel_faturamento
-
010876
(1 row)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] selective updates
Gianluca Riccardi wrote: [cut] solved, i used a function giving as result the wanted value from table2.column1 in a UPDATE table1 SET column4=(my_personal_func()) WHERE 'cirteria on table1.column2 table2.column2' ; sorry for making you wasting your time Gianluca Riccardi ---(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] RETURN SET OF DATA WITH CURSOR
grupos wrote: Hi ! I am making one plpgsql function and I need to return of setof data using a cursor. The problem is that my function is returning only the first row and column of the query. This query have more than 80 columns and 1.000 rows. Enyone have any tip to give me? Yes - decide whether you are returning a set of rows or a refcursor. Check the plpgsql docs again for how to return SETOF using the RETURN NEXT statement. Then you call your function like: SELECT * FROM my_func(); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] RETURN SET OF DATA WITH CURSOR
Hi Richard, Thanks for your promptly answer. I don't have experience returning refcursor but my choice would be it. I read the documentation but I didn't find any example with my necessity... Could you give me a small example on the same "basis" that I want? Thanks, Rodrigo Carvalhaes Richard Huxton wrote: grupos wrote: Hi ! I am making one plpgsql function and I need to return of setof data using a cursor. The problem is that my function is returning only the first row and column of the query. This query have more than 80 columns and 1.000 rows. Enyone have any tip to give me? Yes - decide whether you are returning a set of rows or a refcursor. Check the plpgsql docs again for how to return SETOF using the RETURN NEXT statement. Then you call your function like: SELECT * FROM my_func(); ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] RETURN SET OF DATA WITH CURSOR
grupos wrote: Hi Richard, Thanks for your promptly answer. I don't have experience returning refcursor but my choice would be it. > I read the documentation but I didn't find any example with my necessity... http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html#PLPGSQL-CURSOR-USING See the example in "returning cursors" -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Defaulting a column to 'now'
Thanks, Tom (also Keith Worthington and Bricklen Anderson). That works. ~ Ken > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 14, 2005 1:15 PM > To: Ken Winter > Cc: PostgreSQL pg-sql list > Subject: Re: [SQL] Defaulting a column to 'now' > > "Ken Winter" <[EMAIL PROTECTED]> writes: > > How can a column's default be set to 'now', meaning 'now' as of when > each > > row is inserted? > > You need a function, not a literal constant. The SQL-spec way is > CURRENT_TIMESTAMP > (which is a function, despite the spec's weird idea that it should be > spelled without parentheses); the traditional Postgres way is > now() > > Either way only sets an insertion default, though. If you want to > enforce a correct value on insertion, or change the value when the > row is UPDATEd, you need to use a trigger. > > regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Need SQL Help Finding Current Status of members
I have a table which keeps track of the status of members. In the table is member_id int(8) status_code char(1) status_date date KEY member_id (member_id,status_code,status_date) Each member can have multiple records because a record is added each time the status changes but the old record is kept for history. What I want to do is find the latest status for each member. Actually I want to find all those with an status of "A". But it must be the current (latest) status. How do I find the most current date for each member in a pile of many records for many members with many status settings with one SQL statement? This is a bit beyond my capabilities so I am asking for help. My initial SQL is SELECT * FROM memberstatus WHERE status_code = 'a' but that is my limit. I know an AND comes next but need help after that! I know that MAX is not for dates so that is out. Is there a LATEST DATE? I did not see one when I was looking at the date functions. Appreciate the help. Mike <> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Need SQL Help Finding Current Status of members
On Thu, Dec 15, 2005 at 08:31:09PM -0500, Michael Avila wrote: > What I want to do is find the latest status for each member. Actually I want > to find all those with an status of "A". But it must be the current (latest) > status. How do I find the most current date for each member in a pile of > many records for many members with many status settings with one SQL > statement? Suppose you have this table: SELECT * FROM memberstatus; member_id | status_code | status_date ---+-+- 1 | a | 2005-01-01 2 | x | 2005-01-01 3 | x | 2005-01-01 4 | x | 2005-01-01 1 | x | 2005-12-15 2 | a | 2005-12-15 3 | y | 2005-12-15 4 | a | 2005-12-15 (8 rows) Let's order the data so all of a member's records are shown together, with the latest one first: SELECT * FROM memberstatus ORDER BY member_id, status_date DESC; member_id | status_code | status_date ---+-+- 1 | x | 2005-12-15 1 | a | 2005-01-01 2 | a | 2005-12-15 2 | x | 2005-01-01 3 | y | 2005-12-15 3 | x | 2005-01-01 4 | a | 2005-12-15 4 | x | 2005-01-01 (8 rows) One way to get only the first record for each member is to use PostgreSQL's nonstandard DISTINCT ON construct: SELECT DISTINCT ON (member_id) * FROM memberstatus ORDER BY member_id, status_date DESC; member_id | status_code | status_date ---+-+- 1 | x | 2005-12-15 2 | a | 2005-12-15 3 | y | 2005-12-15 4 | a | 2005-12-15 (4 rows) We could put the above in a subquery and restrict the output to the records we want: SELECT * FROM ( SELECT DISTINCT ON (member_id) * FROM memberstatus ORDER BY member_id, status_date DESC ) AS s WHERE status_code = 'a' ORDER BY member_id; member_id | status_code | status_date ---+-+- 2 | a | 2005-12-15 4 | a | 2005-12-15 (2 rows) This isn't the only way; search the archives for alternatives. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
