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? — , Mar 19 '16 at 06:44
Where and how you are checking `nb_bills` value? Show whole code if it possible. — Abelisto, Mar 19 '16 at 08: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, Mar 20 '16 at 03: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., Mar 20 '16 at 15:58

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

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.

plpgsql : how to reference variable in sql statement

1 Answers1

Better approach