From: | Ron Clarke <rclarkeai(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Moving from TSQL to PL/pgsql select into a variable... |
Date: | 2019-03-18 14:52:56 |
Message-ID: | CAGVf-sPcYowWTuFVuoU3Ho_h4+pXm5BQboYw_=qAu=irEZKF5Q@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
/*
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
*/
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-03-18 14:57:46 | Re: Moving from TSQL to PL/pgsql select into a variable... |
Previous Message | MICHAEL LAZLO | 2019-03-12 16:30:16 | Re: Jsonb column |