Thread: How can this be legal syntax
I was quite amazed to find that this piece of code actually works while reviewing code.<br />I would prefer if it gave anerror :)<br /><br />test=# create or replace function test(i_input text) returns text as <br />$$<br />declare <br /> result text;<br />begin <br /> SELECT<br /> CASE<br /> WHEN lower(i_input) ~ '^[a-z]' THEN 'S'<br /> WHEN i_input ~ '[0-9]' THEN 'N' <br /> ELSE 'ERROR'<br /> INTO result<br /> END;<br /> return result;<br />end; <br />$$<br />language plpgsql security definer;<br />CREATE FUNCTION<br />test=# select * from test('aaaa' );<br /> test <br/>------<br /> S<br />(1 row)<br /><br />test=# create or replace function test(i_input text) returns text as $$<br />declare<br/> result text;<br />begin<br /> SELECT<br /> CASE<br /> WHEN lower(i_input) ~ '^[a-z]' INTOresult THEN 'S'<br /> WHEN i_input ~ '[0-9]' THEN 'N' <br /> ELSE 'ERROR'<br /> END;<br /> returnresult;<br />end;<br />$$ language plpgsql security definer;<br />CREATE FUNCTION<br />test=# select * from test('aaaa');<br /> test <br />------<br /> S<br />(1 row)<br />
Asko Oja escribió: > I was quite amazed to find that this piece of code actually works while > reviewing code. > I would prefer if it gave an error :) Yeah, me too. The functions posted by Josh Drake yesterday about constraint exclusion had something like select * from into temp table ...; (temp is a declared variable name). Nasty. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes: > Asko Oja escribi�: >> I was quite amazed to find that this piece of code actually works while >> reviewing code. >> I would prefer if it gave an error :) > Yeah, me too. As the plpgsql docs say, "The INTO clause can appear almost anywhere in the SQL command". You can write some pretty ugly code if you choose to :-(. Although the docs also threaten to tighten this up, it's been like that for so long that I'm worried about how much user code we'll break if we do. regards, tom lane
On Tue, Dec 16, 2008 at 12:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > it's been like > that for so long that I'm worried about how much user code we'll break > if we do. > code making something like that is actually broken, what a "fix" here will do is making that obvious... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157