Skip site navigation (1) Skip section navigation (2)

Fwd: WIP: CASE statement for PL/pgSQL

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Fwd: WIP: CASE statement for PL/pgSQL
Date: 2008-03-31 15:33:42
Message-ID: 162867790803310833s43dad00eo6f1c509ca5fea61d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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';
 [1]
         RAISE NOTICE '1,2';
         RAISE NOTICE '3';
 [2]
         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

Attachment: case.diff
Description: text/x-patch (16.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2008-03-31 16:33:26
Subject: Re: [PATCHES] Minimum selectivity estimate for LIKE 'prefix%'
Previous:From: Tom LaneDate: 2008-03-31 15:32:43
Subject: Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong

pgsql-patches by date

Next:From: Gregory StarkDate: 2008-03-31 15:34:23
Subject: Re: Consistent \d commands in psql
Previous:From: Brendan JurdDate: 2008-03-31 15:27:31
Subject: Re: Consistent \d commands in psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group