Moving from TSQL to PL/pgsql select into a variable...

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Moving from TSQL to PL/pgsql select into a variable...

Ron Clarke
/*
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) 

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;

EXCEPTION
WHEN NO_DATA_FOUND THEN
myvar := NULL;
    END;
    SELECT myvar;
  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.

Thanks 
*/





Reply | Threaded
Open this post in threaded view
|

Re: Moving from TSQL to PL/pgsql select into a variable...

David G Johnston
n Mon, Mar 18, 2019 at 7:53 AM Ron Clarke <[hidden email]> wrote:
> SELECT cast('a' as varchar) AS X INTO myvar;

That is correct though you'll more often see:

myvar := SELECT cast('1' as varchar);

>     SELECT myvar;

That is the SELECT statement that doesn't have a result.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Moving from TSQL to PL/pgsql select into a variable...

Pavel Stehule
In reply to this post by Ron Clarke
Hi

po 18. 3. 2019 v 15:53 odesílatel Ron Clarke <[hidden email]> 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


Regards

Pavel


 

Thanks 
*/