Re: MERGE ... RETURNING - Mailing list pgsql-hackers
| From | jian he |
|---|---|
| Subject | Re: MERGE ... RETURNING |
| Date | |
| Msg-id | CACJufxF5Gv+j2MRn+Tf1gboDGeY322GAm6_KA+0h9NbKu3zntA@mail.gmail.com Whole thread Raw |
| In response to | Re: MERGE ... RETURNING (Dean Rasheed <dean.a.rasheed@gmail.com>) |
| Responses |
Re: MERGE ... RETURNING
|
| List | pgsql-hackers |
Hi
mainly document issues. Other than that, it looks good!
MERGE not supported in COPY
MERGE not supported in WITH query
These entries in src/backend/po.* need to be deleted if this patch is committed?
------------------------------------------------------
<indexterm zone="dml-returning">
<primary>RETURNING</primary>
</indexterm>
<indexterm zone="dml-returning">
<primary>INSERT</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>UPDATE</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>DELETE</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>MERGE</primary>
<secondary>RETURNING</secondary>
</indexterm>
in doc/src/sgml/dml.sgml, what is the point of these?
It is not rendered in the html file, deleting it still generates all the html file.
------------------------------------------------------
The following part is about doc/src/sgml/plpgsql.sgml.
<para>
The <replaceable>query</replaceable> used in this type of <literal>FOR</literal>
statement can be any SQL command that returns rows to the caller:
<command>SELECT</command> is the most common case,
but you can also use <command>INSERT</command>, <command>UPDATE</command>, or
<command>DELETE</command> with a <literal>RETURNING</literal> clause. Some utility
commands such as <command>EXPLAIN</command> will work too.
</para>
here we need to add <command>MERGE</command>?
<para>
Row-level triggers fired <literal>BEFORE</literal> can return null to signal the
trigger manager to skip the rest of the operation for this row
(i.e., subsequent triggers are not fired, and the
<command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> does not occur
for this row). If a nonnull
here we need to add <command>MERGE</command>?
<para>
Variable substitution currently works only in <command>SELECT</command>,
<command>INSERT</command>, <command>UPDATE</command>,
<command>DELETE</command>, and commands containing one of
these (such as <command>EXPLAIN</command> and <command>CREATE TABLE
... AS SELECT</command>),
because the main SQL engine allows query parameters only in these
commands. To use a non-constant name or value in other statement
types (generically called utility statements), you must construct
the utility statement as a string and <command>EXECUTE</command> it.
</para>
here we need to add <command>MERGE</command>?
demo:
CREATE OR REPlACE FUNCTION stamp_user2(id int, comment text) RETURNS void AS $$
<<fn>>
DECLARE
curtime timestamp := now();
BEGIN
MERGE INTO users
USING (SELECT 1)
ON true
WHEN MATCHED and (users.id = stamp_user2.id) THEN
update SET last_modified = fn.curtime, comment = stamp_user2.comment;
raise notice 'test';
END;
$$ LANGUAGE plpgsql;
<literal>INSTEAD OF</literal> triggers (which are always row-level triggers,
and may only be used on views) can return null to signal that they did
not perform any updates, and that the rest of the operation for this
row should be skipped (i.e., subsequent triggers are not fired, and the
row is not counted in the rows-affected status for the surrounding
<command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>).
I am not sure we need to add <command>MERGE</command >. Maybe not.
MERGE not supported in COPY
MERGE not supported in WITH query
These entries in src/backend/po.* need to be deleted if this patch is committed?
------------------------------------------------------
<indexterm zone="dml-returning">
<primary>RETURNING</primary>
</indexterm>
<indexterm zone="dml-returning">
<primary>INSERT</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>UPDATE</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>DELETE</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>MERGE</primary>
<secondary>RETURNING</secondary>
</indexterm>
in doc/src/sgml/dml.sgml, what is the point of these?
It is not rendered in the html file, deleting it still generates all the html file.
------------------------------------------------------
The following part is about doc/src/sgml/plpgsql.sgml.
<para>
The <replaceable>query</replaceable> used in this type of <literal>FOR</literal>
statement can be any SQL command that returns rows to the caller:
<command>SELECT</command> is the most common case,
but you can also use <command>INSERT</command>, <command>UPDATE</command>, or
<command>DELETE</command> with a <literal>RETURNING</literal> clause. Some utility
commands such as <command>EXPLAIN</command> will work too.
</para>
here we need to add <command>MERGE</command>?
<para>
Row-level triggers fired <literal>BEFORE</literal> can return null to signal the
trigger manager to skip the rest of the operation for this row
(i.e., subsequent triggers are not fired, and the
<command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> does not occur
for this row). If a nonnull
here we need to add <command>MERGE</command>?
<para>
Variable substitution currently works only in <command>SELECT</command>,
<command>INSERT</command>, <command>UPDATE</command>,
<command>DELETE</command>, and commands containing one of
these (such as <command>EXPLAIN</command> and <command>CREATE TABLE
... AS SELECT</command>),
because the main SQL engine allows query parameters only in these
commands. To use a non-constant name or value in other statement
types (generically called utility statements), you must construct
the utility statement as a string and <command>EXECUTE</command> it.
</para>
here we need to add <command>MERGE</command>?
demo:
CREATE OR REPlACE FUNCTION stamp_user2(id int, comment text) RETURNS void AS $$
<<fn>>
DECLARE
curtime timestamp := now();
BEGIN
MERGE INTO users
USING (SELECT 1)
ON true
WHEN MATCHED and (users.id = stamp_user2.id) THEN
update SET last_modified = fn.curtime, comment = stamp_user2.comment;
raise notice 'test';
END;
$$ LANGUAGE plpgsql;
<literal>INSTEAD OF</literal> triggers (which are always row-level triggers,
and may only be used on views) can return null to signal that they did
not perform any updates, and that the rest of the operation for this
row should be skipped (i.e., subsequent triggers are not fired, and the
row is not counted in the rows-affected status for the surrounding
<command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>).
I am not sure we need to add <command>MERGE</command >. Maybe not.
pgsql-hackers by date: