Thread: unable to assign value to variable in plpgsql

Hi, I am trying to assign number of row as integer Tried 1 maz int :=(SELECT count(col1) FROM table WHERE col1 = quote_literal(in val1)); Tried 2 EXECUTE 'SELECT count(col1) FROM table WHERE col1 = quote_literal(val1) INTO maz'; getting error INTO Regards Chirag

---

View this message in context: unable to assign value to variable in plpgsql
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Hi, I am trying to assign number of row as integer Tried 1 maz int :=(SELECT count(col1) FROM table WHERE col1 = quote_literal(in val1)); Tried 2 EXECUTE 'SELECT count(col1) FROM table WHERE col1 = quote_literal(val1) INTO maz'; getting error INTO Regards Chirag

---

View this message in context: unable to assign value to variable in plpgsql
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

SELECT INTO int count(*) FROM table WHERE col1 = val1;

or

int := (SELECT count(*) FROM table WHERE col1 = val1);

-- 
Vik

On Sun, Feb 16, 2014 at 04:22:28AM -0800, Chirag Mittal wrote:
>Hi, I am trying to assign number of row as integer Tried 1 maz int :=(SELECT
>count(col1) FROM table WHERE col1 = quote_literal(in val1)); Tried 2 EXECUTE
>'SELECT count(col1) FROM table WHERE col1 = quote_literal(val1) INTO maz';
>getting error INTO Regards Chirag

select count(*)
into maz
from tablename
where col1 = 'val1';

maz int := count(*) from tablename where col1 = 'val1';

M.

Chirag Mittal wrote
> Hi,
>
> I am trying to assign number of row as integer
>
> Tried 1
> maz int :=(SELECT count(col1) FROM table WHERE col1 = quote_literal(in
> val1));

Use of quote_literal is pointless.
Prefixing variable name with in is wrong.  In/out are only used in function
declarations.  While not explicitly documented all of the examples that use
input variables show the proper usage.
Specifying a type after a variable name is ok if you doing this in the
declare section, I think.  Usually easier to relegate all queries to the
main body in which case the type specifier is wrong.


> Tried 2
>
> EXECUTE 'SELECT count(col1) FROM table WHERE col1 = quote_literal(val1)
> INTO maz';
> getting error INTO
>
> Regards
> Chirag

INTO belongs outside of the command string.
Instead of quote_literal you would usually want to use a parameter here for
val1 and add a USING clause.

Have you read the documentation for pl/pgsql? It is quite thorough and even
has section headers that nearly match your question so that learning how to
do this should be easy to find.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/unable-to-assign-value-to-variable-in-plpgsql-tp5792281p5792296.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Martin Steer wrote
> On Sun, Feb 16, 2014 at 04:22:28AM -0800, Chirag Mittal wrote:
>>Hi, I am trying to assign number of row as integer Tried 1 maz int
:=(SELECT
>>count(col1) FROM table WHERE col1 = quote_literal(in val1)); Tried 2
EXECUTE
>>'SELECT count(col1) FROM table WHERE col1 = quote_literal(val1) INTO maz';
>>getting error INTO Regards Chirag
>
> select count(*)
> into maz
> from tablename
> where col1 = 'val1';
>
> maz int := count(*) from tablename where col1 = 'val1';
>
> M.

Note that if val1 is a variable you do not use quotes. Only when it is a
literal/constant.  Using quotes makes it into a literal/constant.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/unable-to-assign-value-to-variable-in-plpgsql-tp5792281p5792299.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Thanks everyone for your help, I did the following and it worked


maz := (SELECT count(*) FROM table WHERE col1 = var1);
EXECUTE 'SELECT count(*) FROM table WHERE col1 = $1'
USING col1 INTO maz;

"in" was a typo and var type along with query was under declare (hope this
is right practice techincaly)





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/unable-to-assign-value-to-variable-in-plpgsql-tp5792281p5792298.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.