[BUGS] feature request

#1

hi all

it would be good to have RETURNING clause in INSERT,UPDATE,DELETE queries.
similar to Oracle's one but more clever.
Oracle's "RETURNING" is just a disguised independant SELECT wich is not=20
wanted.
i found "RETURNING" usable if it returns just inserted or just deleted reco=
rd.
e.g. i have some DEFAULTS in field declarations and want to see what values=
=20
actually sot by the last insert.
now i forced to call SELECT after insert.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#3

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#4

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#5

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#6

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#7

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#8

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#9

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#10

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

Quote:

There is situation where I check values and if they do not satisfy some
condition I need to set default.
What is the best way to do that?

don't do it

One advice for triggers - do only check of values and newer fix or
correct values. It's general advice. I like triggers, but triggers
have to be clean. Too much smart triggers uncleans bussiness logic.
Try to move some logic from trigger to some function (stored
function).

It's not easy get default values in body of trigger, because any DML
statetement can start trigger recursion. I don't know clean solution.
Maybe use function for default value, if it's possible.

create or replace function default_generator() ...

create table foo(a integer default default_generator())

and then:

new.id = default_generator();

it's probably one clean solution

Pavel

Quote:

----- Original Message ----- From: "Pavel Stehule" <pavel.stehule (AT) gmail (DOT) com
To: <Eugen.Konkov (AT) aldec (DOT) com
Cc: <pgsql-bugs (AT) postgresql (DOT) org
Sent: Monday, May 05, 2008 8:10 PM
Subject: Re: [BUGS] Feature request

Hello

2008/5/5 <Eugen.Konkov (AT) aldec (DOT) com>:

So why I can do:
insert into (id) values ( default )
and can not do in my trigger:
new.id = default
Why?

it's not possible - because you cannot mix PL/pgSQL statement
(assignment) and SQL stetement.

syntax of assignment
:
variable := expression

expression is independent on variable, so there are not any "default"
expression. PL/pgSQL and SQL are independent environments. If you need
default values, simply don't change field.

Regards
Pavel Stehule

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs