plpgsql : how to reference variable in sql statement

Question

I am rather new to PL/pgSQL and don't know how to reference a variable in a SELECT statement.

In this following function the SELECT INTO always returns NULL:

$body$
DECLARE
 r RECORD;
 c CURSOR FOR select name from t_people;
 nb_bills integer;
BEGIN
 OPEN c;
 LOOP
   FETCH c INTO r;
   EXIT WHEN NOT FOUND;

   RAISE NOTICE 'name found: %', r.name; 
   SELECT count(bill_id) INTO nb_bills from t_bills where name = r.name;
 END LOOP;

END;
$body$

RAISE NOTICE allows me to verify that my CURSOR is working well: names are properly retrieved, but for some reason still unknown to me, not properly passed to the SELECT INTO statement.

For debugging purpose, I tried to replace the variable in SELECT INTO with a constant value and it worked:

SELECT count( bill_id) INTO nb_bills from t_bills where name = 'joe';

I don't know how to reference r.name in the SELECT INTO statement. I tried r.name, I tried to create another variable containing quotes, it is always returning NULL.
I am stuck. If anyone knows ...

The reference r.name is correct. When you run select p.name, count(b.bill_id) from t_people p join t_bills b on t.name = b.name group by name in a SQL client, what does it return? — user330315
– user330315, Commented Mar 19, 2016 at 6:44
Where and how you are checking nb_bills value? Show whole code if it possible. — Abelisto
– Abelisto, Commented Mar 19, 2016 at 8:12
The header showing IN and OUT parameters, RETURN type, language declaration etc. is an essential part of the function. Remember to always include it - as well as your version of Postgres. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 20, 2016 at 3:04
Hi an thanks for your reply. What I mean to say is the following: -- r.name contains 'joe' SELECT count(bill_id) INTO nb_bills from t_bills where name = r.name; RAISE NOTICE 'nb_bills: %', nb_bills; -- shows NULL but SELECT count(bill_id) INTO nb_bills from t_bills where name = 'joe'; RAISE NOTICE 'nb_bills: %', nb_bills; -- shows 5 Is there a way I could see the SQL query submitted ? I tried to activate the log feature but it does seem not show this kind of SQL statement. Only call to the procedure is logged. — Jerome C.
– Jerome C., Commented Mar 20, 2016 at 15:58

Community · Accepted Answer · 2020-06-20 09:12:55Z

the SELECT INTO always returns NULL

not properly passed to the SELECT INTO statement.

it is always returning NULL.

None of this makes sense.

SELECT statements do not return anything by itself in PL/pgSQL. You have to either assign results to variables or explicitly return results with one of the available RETURN variants.

SELECT INTO is only used for variable assignment and does not return anything, either. Not to be confused with SELECT INTO in plain SQL - which is generally discouraged:

Combine two tables into a new one so that select rows from the other one are ignored

It's not clear what's supposed to be returned in your example. You did not disclose the return type of the function and you did not actually return anything.

Start by reading the chapter Returning From a Function in the manual. Here are some related answers with examples:

And there may be naming conflicts with parameter names. We can't tell unless you post the complete function definition.

Better approach

That said, explicit cursors are only actually needed on rare occasions. Typically, the implicit cursor of a FOR loop is simpler to handle and cheaper:

Truncating all tables in a Postgres database

And most of the time you don't even need any cursors or loops at all. Set-based solutions are typically simpler and faster.

Collectives™ on Stack Overflow

plpgsql : how to reference variable in sql statement

1 Answer 1

Better approach

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Better approach

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related