Обсуждение: 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
*/
n Mon, Mar 18, 2019 at 7:53 AM Ron Clarke <rclarkeai@gmail.com> 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.
Hi
po 18. 3. 2019 v 15:53 odesílatel Ron Clarke <rclarkeai@gmail.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;BEGINBEGIN-- 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);
EXCEPTIONWHEN NO_DATA_FOUND THENmyvar := 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 dataSQL state: 42601Hint: 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;BEGINBEGIN-- SELECT cast('a' as varchar) AS X INTO myvar;PERFORM cast('a' as varchar) AS X INTO myvar;EXCEPTIONWHEN NO_DATA_FOUND THENmyvar := NULL;END;SELECT myvar;END;$$LANGUAGE plpgsql ;/*ERROR: query "SELECT cast('a' as varchar) AS X INTO myvar" is not a SELECTSQL state: 42601Context: 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*/