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

/*

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 

*/