From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Ron Clarke <rclarkeai(at)gmail(dot)com> |
Cc: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: Moving from TSQL to PL/pgsql select into a variable... |
Date: | 2019-03-18 15:06:19 |
Message-ID: | CAFj8pRC1DWoPzatYG8xjg2toU99SQmNiYydyPxUpcZzTBwU5Sg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi
po 18. 3. 2019 v 15:53 odesílatel Ron Clarke <rclarkeai(at)gmail(dot)com> napsal:
> /*
> I'm trying to get to grips with the world of pgsql and have lots of habits
> and techniques from a lifetime of SQL Server's TSQL.
>
> Anyway - I'm trying to assign a variable with the value returned by a
> select statement to be used subsequently within a pl/pgsql code block..
> (in real life I want to maniupulate json objects)
>
you cannot to do it. There is lot of differences between PL/pgSQL and T-SQL.
a) DO statements doesn't support returning value
b) T-SQL procedures allows returns multirecord set as result of unbound
queries. Nothing similar is in PL/pgSQL (nad PL/SQL (Oracle) or DB2).
> To keep this absolutely simple as an example let's assign the value 'a' to
> my variable from a select so :
>
> SELECT cast('a' as varchar) AS X;
>
> Then if I do the following:
>
> */
>
> DO $$
> DECLARE myvar varchar;
> BEGIN
> BEGIN
> -- Try SELECT 'a' INTO myvar;
> SELECT cast('a' as varchar) AS X INTO myvar;
>
don't do it, use assign statement - it is faster and cleaner
myvar := cast('a' as varchar);
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> myvar := NULL;
> END;
> SELECT myvar;
>
returning is not supported in this case. In other cases, there is RETURN
statement
END;
> $$
> LANGUAGE plpgsql ;
>
> /*
>
> ERROR: query has no destination for result data
> SQL state: 42601
> Hint: If you want to discard the results of a SELECT, use PERFORM instead.
> Context: PL/pgSQL function inline_code_block line 13 at SQL statement
>
> */
>
> DO $$
> DECLARE myvar varchar;
> BEGIN
> BEGIN
> -- SELECT cast('a' as varchar) AS X INTO myvar;
> PERFORM cast('a' as varchar) AS X INTO myvar;
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> myvar := NULL;
> END;
> SELECT myvar;
> END;
> $$
> LANGUAGE plpgsql ;
>
> /*
> ERROR: query "SELECT cast('a' as varchar) AS X INTO myvar" is not a SELECT
> SQL state: 42601
> Context: PL/pgSQL function inline_code_block line 7 at PERFORM
> */
>
> /*
>
> I'm guessing that I'm using the wrong approach here and fighting pqsql as
> opposed to using it, so can anyone point me in the right direction as to
> what I should be doing to select and assign a value to a variable as I've
> managed to confuse myself now.
>
It is little bit joke, but all what you know from T-SQL is better to
forgot, if you want to write code in PLpgSQL. Please, try to read doc. It
is good enough
https://www.postgresql.org/docs/9.6/plpgsql.html
Regards
Pavel
>
> Thanks
> */
>
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | pavan95 | 2019-03-22 11:26:04 | Re: Error while executing a view in postgres 10.5 |
Previous Message | David G. Johnston | 2019-03-18 14:57:46 | Re: Moving from TSQL to PL/pgsql select into a variable... |