Re: REPLACE - Mailing list 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
ofText with the field names.* @param $2 Array of Text with the field values.* @return ID of the primary key of
inserted/updatedcolumn.* @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