pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreplace??
6+ messages / 6 participants
[nested] [flat]
* replace??
@ 2000-12-19 12:37 Bruno Boettcher <bboett@erm1.u-strasbg.fr>
2000-12-19 13:08 ` Re: replace?? Brett W. McCoy <bmccoy@chapelperilous.net>
0 siblings, 1 reply; 6+ messages in thread
From: bboett@erm1.u-strasbg.fr @ 2000-12-19 12:37 UTC (permalink / raw)
To: pgsql-sql
hello,
i have the following problem:
i have in php an array structure with user-prefs that have to be stored
onto DB....
actually i look up if the corresponding entry exists (comparing
user-name and field-name) if yes i update, if no i insert....
this isn't very appealing, but i couldn't find another way yet to make
this shorter...
would be nice if something like the following existed:
replace settings set auser='toto',field='lang',data='fr' where
auser='toto' and field='lang';
update settings set auser='toto',field='lang',data='fr' where
auser='toto' and field='lang' REPLACE;
or even better:
replace settings keys(auser,field) set auser='toto',field='lang',data='fr';
--
ciao bboett
==============================================================
bboett@earthling.net
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===============================================================
the total amount of intelligence on earth is constant.
human population is growing....
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: replace??
2000-12-19 12:37 replace?? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
@ 2000-12-19 13:08 ` Brett W. McCoy <bmccoy@chapelperilous.net>
0 siblings, 0 replies; 6+ messages in thread
From: Brett W. McCoy @ 2000-12-19 13:08 UTC (permalink / raw)
To: Bruno Boettcher <bboett@erm1.u-strasbg.fr>; +Cc: pgsql-sql
On Tue, 19 Dec 2000, Bruno Boettcher wrote:
> actually i look up if the corresponding entry exists (comparing
> user-name and field-name) if yes i update, if no i insert....
>
> this isn't very appealing, but i couldn't find another way yet to make
> this shorter...
>
> would be nice if something like the following existed:
>
> replace settings set auser='toto',field='lang',data='fr' where
> auser='toto' and field='lang';
>
> update settings set auser='toto',field='lang',data='fr' where
> auser='toto' and field='lang' REPLACE;
What is the difference between replacing data and updating data in this
sense?
What you might want to do with your array (which in PHP can also function
as a hash, with string indices) is create a wrapper class that kind of
emulates a hash tied to the DB like one might do in Perl, so that whenever
you 'store' a value in the array (via a method), it will automagically
update or insert into the underlying database. PHP doesn't do ties
explicitly, but you can emulate this behaviour with a PHP class.
-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
The price of greatness is responsibility.
^ permalink raw reply [nested|flat] 6+ messages in thread
* REPLACE
@ 2003-04-14 12:50 Radovan Petrikí <r.petrik@episoftware.com>
0 siblings, 0 replies; 6+ messages in thread
From: Radovan Petrikí @ 2003-04-14 12:50 UTC (permalink / raw)
To: pgsql-sql
Hi,
In mysql exist this query
"REPLACE name_table (id,colum) VALUES(1,'test')";
When row id=1 exist then replace row.
When row id!=1 then insert row.
How this make in PostgreSQL ?
Rado.
^ permalink raw reply [nested|flat] 6+ messages in thread
* REPLACE
@ 2003-04-14 13:09 Rado Petrik <r.p@szm.sk>
2003-04-14 15:20 ` Re: REPLACE Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 6+ messages in thread
From: Rado Petrik @ 2003-04-14 13:09 UTC (permalink / raw)
To: pgsql-sql
Hi,
In mysql exist this query
"REPLACE name_table (id,colum) VALUES(1,'test')";
When row id=1 exist then replace row.
When row id!=1 then insert row.
How this make in PostgreSQL ?
Rado.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: REPLACE
2003-04-14 13:09 REPLACE Rado Petrik <r.p@szm.sk>
@ 2003-04-14 15:20 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 0 replies; 6+ messages in thread
From: Franco Bruno Borghesi @ 2003-04-14 15:20 UTC (permalink / raw)
To: Rado Petrik <r.p@szm.sk>; pgsql-sql
this is an unstandard feature, but you could solve your problem using
functions. I've created this function that does what you need:
/**
* This function does what mysql replace does.
* @param $1 Name of the table where to update/insert
* @param $2 Array of Text with the field names.
* @param $2 Array of Text with the field values.
* @return ID of the primary key of inserted/updated column.
* @note the first element in the fields and values arrays belong
* to the primary key field.
*/
CREATE OR REPLACE FUNCTION myReplace(TEXT, TEXT[], TEXT[]) RETURNS INTEGER AS
'
DECLARE
a_tableName ALIAS FOR $1;
a_fields ALIAS FOR $2;
a_values ALIAS FOR $3;
v_sql TEXT DEFAULT '''';
v_rec RECORD;
v_count INTEGER DEFAULT 1;
v_exists BOOLEAN DEFAULT false;
r_result INTEGER;
BEGIN
r_result:=a_values[1];
WHILE (a_fields[v_count] IS NOT NULL) LOOP
v_count:=v_count+1;
END LOOP;
v_count:=v_count-1;
v_sql:=''SELECT '' || a_fields[1] || '' FROM '' || a_tableName || '' WHERE
'' || a_fields[1] || ''='''''' || a_values[1] || '''''''';
--find records
FOR v_rec IN
EXECUTE v_sql
LOOP
v_exists:=true;
EXIT;
END LOOP;
--does record exist?
IF (v_exists) THEN
--update it!
v_sql:=''UPDATE '' || a_tableName || '' SET '';
FOR i IN 1..v_count
LOOP
v_sql:=v_sql || a_fields[i] || ''='''''' || a_values[i] || '''''''';
IF (i < v_count) THEN
v_sql:=v_sql || '', '';
END IF;
END LOOP;
v_sql:=v_sql || '' WHERE '' || a_fields[1] || ''='''''' || a_values[1]
|| '''''''';
ELSE --record does not exist
--insert a new one
v_sql:=''INSERT INTO '' || a_tableName || '' ('';
FOR i IN 1..v_count
LOOP
v_sql:=v_sql || a_fields[i];
IF (i < v_count) THEN
v_sql:=v_sql || '', '';
END IF;
END LOOP;
v_sql:=v_sql || '') VALUES ('';
FOR i IN 1..v_count
LOOP
v_sql:=v_sql || '''''''' || a_values[i] || '''''''';
IF (i < v_count) THEN
v_sql:=v_sql || '', '';
END IF;
END LOOP;
v_sql:=v_sql || '')'';
END IF;
RAISE NOTICE ''v_sql: % '', v_sql;
EXECUTE v_sql;
RETURN r_result;
END;
' LANGUAGE 'plpgsql';
Examples:
SELECT myReplace('mytable', '{"id", "name", "foo"}', '{"1", "peter", "25"}');
SELECT myReplace('mytable', '{"id", "name", "foo"}', '{"1", "peter", "27"}');
On Monday 14 April 2003 10:09, Rado Petrik wrote:
> Hi,
>
> In mysql exist this query
> "REPLACE name_table (id,colum) VALUES(1,'test')";
>
> When row id=1 exist then replace row.
> When row id!=1 then insert row.
>
> How this make in PostgreSQL ?
>
> Rado.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 6+ messages in thread
* REPLACE
@ 2004-01-31 21:50 LawyerKill <lawyerkill@aol.com>
0 siblings, 0 replies; 6+ messages in thread
From: lawyerkill@aol.com @ 2004-01-31 21:50 UTC (permalink / raw)
To: pgsql-sql
LK
I'm using Foxpro 8 and I'm trying to merge selective data from 4 different
tables into a new table.The problem is that the tables are not alike, Table one
may have 4 records with the name John in them, table 2 had only one John with
one account # in that record, so I want to have a new table with the 4
Johns(Same Johns) and the account number each time after his name. So here is
what I did. I selected data from each table and stored them in an array, 3 of
the four table have a common field which is, 'Name'
Create table data
Name char (12),;
Address char (20),;
Phone Char (10),;
Account Char (20),;
Order N(15),;
Date Date,;
(etc.)
Select Name, Address, Phone (etc.) From Table 1;
where Table1.date= todays date;
INTO ARRAY ABC
(Then I count the number of records)
Num1=Alen(ABC,1)
Select Name, AccountN, Order, time (etc) from Table2;
Where Table2.date= todays date;
INTO ARRAY DFE
(Then I count the number of records)
Num2=Alen(DFE,1)
Now since Table 1 has the most data I move that data into the new table.
Use Data
Append blank
For XXX=1 to Num1(Record count from array)
Replace Name with ABE(xxx,1)
Replace Address with ABE(xxx,2)
Replace Phone with ABE(xxx,3)
(Etc)
Skip
Endfor|Next
So far so good, now here is where the problem happens. Let's say Johnname
appears 4 times in table 1, and had one account Num in table 2, lets say it's
ASD234 and I have 36 records all together from Table 1. Now I try and replace
the account number, there are only 9 different records in table 2, 9 account
numers to match to the 36 records from Table 1.
For SA = 1 to Num2(Second array count total of 9 records)
Replace Account with DEF[SA,1] Where ABC[1,1]=DEF[SA,1]
ENDFOR|Next
Now what happens is that it replaces all 36 records with the same account
number, ASD234, it seems to ignore the, ABC[1,1]=DEF SA[SA,1]. What I was
looking for was this
John ASD234
John ASD234
John ASD234
John ASD234
Joe (no number)
Joe (no number)
Mike (no number)
Etc.
What I get is this,
John ASD234
John ASD234
John ASD234
John ASD234
Joe ASD234
Joe ASD234
Mike ASD234
Etc.
I also tried to use the For command, for ABC[1,1]=DEF SA[SA,1].
I tried using the Locate command and the Insert command, but same
thing, it doesn't stop replacing account wqith the same account number, all 36
records.
For some reason it's not comparing the two expressions like it should and
stopping when they no longer equal each other. Maybe you can't compare 2 arrays
like I'm trying to do. I would be most thankful if anyone can solve this
problem.
Any idea why it's ignore the equal sign and just replacing them all?
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-01-31 21:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-19 12:37 replace?? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
2000-12-19 13:08 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2003-04-14 12:50 REPLACE Radovan Petrikí <r.petrik@episoftware.com>
2003-04-14 13:09 REPLACE Rado Petrik <r.p@szm.sk>
2003-04-14 15:20 ` Re: REPLACE Franco Bruno Borghesi <franco@akyasociados.com.ar>
2004-01-31 21:50 REPLACE LawyerKill <lawyerkill@aol.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox