WIP: CASE statement for PL/pgSQL
Hello
I finished this patch.
Proposal: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php
It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI).
CASE statements is parsed and transformed to CASE expression and
statements paths. Result of CASE expression is used as index to array
of statements paths. It's fast but I have to once time reparse SQL
queries - it generate about 150 lines code, because I need to get all
parameter's positions. It's one disadvantage. On second hand, this
statement needs only one expression evaluation.
Sample:
CREATE OR REPLACE FUNCTION foo(int)
RETURNS void AS $$
BEGIN
CASE $1
WHEN 1,2,3 THEN
RAISE NOTICE '1,2';
RAISE NOTICE '3';
WHEN 4 THEN
RAISE NOTICE '4';
ELSE
RAISE NOTICE 'other than 1,2,3,4';
END CASE;
RETURN;
END;
$$ LANGUAGE plpgsql;
This statement is transformated to:
three statement paths:
[0]: RAISE NOTICE 'other than 1,2,3,4';
RAISE NOTICE 'other than 1,2,3,4';
[1]: RAISE NOTICE '1,2'; RAISE NOTICE '3';
RAISE NOTICE '1,2';
RAISE NOTICE '3';
[2]: RAISE NOTICE '4';
RAISE NOTICE '4';
and case expression
CASE $1
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 2
END;
When result is NULL then it uses 0 path.
Questions:
a) is possible to use SQL scanner? Now, scanner isn't directly used everywhere.
any notes and comments are welcome
Regards
Pavel Stehule
Attachments:
case.difftext/x-patch; name=case.diffDownload+468-14
correct queue
Hello
I finished this patch.
Proposal: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php
It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI).
CASE statements is parsed and transformed to CASE expression and
statements paths. Result of CASE expression is used as index to array
of statements paths. It's fast but I have to once time reparse SQL
queries - it generate about 150 lines code, because I need to get all
parameter's positions. It's one disadvantage. On second hand, this
statement needs only one expression evaluation.
Sample:
CREATE OR REPLACE FUNCTION foo(int)
RETURNS void AS $$
BEGIN
CASE $1
WHEN 1,2,3 THEN
RAISE NOTICE '1,2';
RAISE NOTICE '3';
WHEN 4 THEN
RAISE NOTICE '4';
ELSE
RAISE NOTICE 'other than 1,2,3,4';
END CASE;
RETURN;
END;
$$ LANGUAGE plpgsql;
This statement is transformated to:
three statement paths:
[0]: RAISE NOTICE 'other than 1,2,3,4';
RAISE NOTICE 'other than 1,2,3,4';
[1]: RAISE NOTICE '1,2'; RAISE NOTICE '3';
RAISE NOTICE '1,2';
RAISE NOTICE '3';
[2]: RAISE NOTICE '4';
RAISE NOTICE '4';
and case expression
CASE $1
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 2
END;
When result is NULL then it uses 0 path.
Questions:
a) is possible to use SQL scanner? Now, scanner isn't directly used everywhere.
any notes and comments are welcome
Regards
Pavel Stehule
Attachments:
case.difftext/x-patch; name=case.diffDownload+468-14
Pavel Stehule wrote:
correct queue
Hello
I finished this patch.
Proposal: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php
It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI).
At the very least this patch is missing documentation and regression tests.
cheers
andrew
Hello
On 31/03/2008, Andrew Dunstan <andrew@dunslane.net> wrote:
Pavel Stehule wrote:
correct queue
Hello
I finished this patch.
Proposal:
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php
It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI).
At the very least this patch is missing documentation and regression tests.
yes, I know. Regress tests are not problem. This patch is only WIP and
I'll to update this patch after commiting EXECUTE USING patch. But
somebody maybe can comment this patch now, and I can save some time
later.
Pavel
Show quoted text
cheers
andrew