- it can be PostgreSQL specific syntax - full control over design - maximally robust against typo - long syntax, but for short syntax can be used SELECT c1,c2,c3, .. INTO STRICT recvar
I don't think overloading a SQL command is a good idea. We'd be in trouble if ANSI ever introduced :=. I think that could also conflict with existing operators.
The ":=" operator is used ANSI/SQL already for named arguments. Isn't probable so ANSI uses it in different context.
This is not overloading of SQL command - it is like annotations. It is smart idea, so I was not surprised if ANSI/SQL reuses it. There is not any possible construct, that is robust against typo - because assignment is very verbose and natural.
ANSI - SQL/PSM uses two methods
1. multiassignment
SET (a,b,c) = (SELECT a, b, c ...)
2. auto variables in dedicated new scope
FOR scope_label IN SELECT a, b, c
DO
-- you can use variables a, b, c
-- you can use qualified variables scope_label.a, scope_label.b, ..
END FOR
This method is not possible in PL/pgSQL - but a work with record type is similar
- what should be no_data_found behave?
Considering where we're at today, I don't think there should be a default behavior; make the user specify somehow whether missing data is allowed or not.
I have nothing about a cost of "new syntax" implementation - but for me - it looks like good solution for us - it can be win/win solution. It breaks nothing - it introduce nice to have typo robust syntax.
Related to that, I suspect we could add better support to existing commands for at least some of these things. For example, SELECT ... INTO NOMULTI (instead of STRICT) to indicate that multiple rows are an error but missing data is
Another flag into NOMULTI can be solution too.
The new syntax ":=" has some advantages:
1. it robust against type - it is harder to do unwanted swap of variables, and this mistake is very clear
2. the syntax ensure equality of target variables and source expressions.
I see valuable benefit of this syntax
Regards
Pavel
OK.
-- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
