From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Rado Petrik <r.p@szm.sk>
To: pgsql-sql@postgresql.org
Subject: Re: REPLACE
Date: Mon, 14 Apr 2003 12:20:02 -0300
Message-ID: <200304141220.04046.franco@akyasociados.com.ar> (raw)
In-Reply-To: <1050325783.1035.19.camel@caj>
References: <1050325783.1035.19.camel@caj>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar, r.p@szm.sk
Subject: Re: REPLACE
In-Reply-To: <200304141220.04046.franco@akyasociados.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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