From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Organization: AK y Asociados S.R.L.
To: Rado Petrik <r.p@szm.sk>, pgsql-sql@postgresql.org
Subject: Re: REPLACE
Date: Mon, 14 Apr 2003 12:20:02 -0300
User-Agent: KMail/1.5
References: <1050325783.1035.19.camel@caj>
In-Reply-To: <1050325783.1035.19.camel@caj>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304141220.04046.franco@akyasociados.com.ar>

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