Re: REPLACE

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly