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
