PostgreSQL - Syntax error while assigning variable?

Question

This is my code:

amount INTEGER;

amount := select count(*) from moneyTable;

I'm getting the following error:

ERROR:   syntax error at or near "select"

Can someone help me out.

@MrsEd im trying to store the number of tuples in the variable amount. Then use that variable later for other processing. But currently im getting that syntax error and I have no idea why this is happening. — Brandon
– Brandon, Commented Nov 18, 2015 at 1:25

mu is too short · Accepted Answer · 2015-11-18 01:22:47Z

2

From the fine manual:

An assignment of a value to a PL/pgSQL variable is written as:
variable { := | = } expression;

but select ... isn't an expression. If you want to assign values from a SELECT to variables, you want to use INTO:

select count(*) into amount from moneyTable;
--              ^^^^^^^^^^^

answered Nov 18, 2015 at 1:22

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brandon Over a year ago

Now im getting the error ERROR: syntax error at or near " * ". CONTEXT: invalid type name "count(*) into amount from moneyTable"

mu is too short Over a year ago

Should work fine (sqlfiddle.com/#!15/6558d/1 (sorry about the function formatting, sqlfiddle has problems with multi-line dollar-quoted strings it seems)). You're doing this inside a function, right?

Brandon Over a year ago

I have it in the declaration part of my function

mu is too short Over a year ago

You should have that assignment in the body of the function. A bit of time with the PL/pgSQL documentation might be useful as well.

Community · Accepted Answer · 2017-05-23 12:23:10Z

1

Either the @mu is to short answer or this

amount := (select count(*) from moneyTable);

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Nov 18, 2015 at 4:41

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Syntax error while assigning variable?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related