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

How can this be legal syntax

From: "Asko Oja" <ascoja(at)gmail(dot)com>
To: pgsql <pgsql-sql(at)postgresql(dot)org>
Subject: How can this be legal syntax
Date: 2008-12-16 17:00:18
Message-ID: ecd779860812160900y3acfef7aj14de2d2f61af1d75@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
I was quite amazed to find that this piece of code actually works while
reviewing code.
I would prefer if it gave an error :)

test=# create or replace function test(i_input text) returns text as
$$
declare
    result text;
begin
    SELECT
        CASE
        WHEN lower(i_input) ~ '^[a-z]' THEN 'S'
        WHEN i_input ~ '[0-9]' THEN 'N'
        ELSE 'ERROR'
        INTO result
        END;
    return result;
end;
$$
language plpgsql security definer;
CREATE FUNCTION
test=# select * from test('aaaa' );
 test
------
 S
(1 row)

test=# create or replace function test(i_input text) returns text as $$
declare
    result text;
begin
    SELECT
        CASE
        WHEN lower(i_input) ~ '^[a-z]' INTO result THEN 'S'
        WHEN i_input ~ '[0-9]' THEN 'N'
        ELSE 'ERROR'
        END;
    return result;
end;
$$ language plpgsql security definer;
CREATE FUNCTION
test=# select * from test('aaaa' );
 test
------
 S
(1 row)

Responses

pgsql-sql by date

Next:From: Alvaro HerreraDate: 2008-12-16 17:05:43
Subject: Re: How can this be legal syntax
Previous:From: George PavlovDate: 2008-12-16 00:06:19
Subject: Re: create table with rownames as values in column of seciond table

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