1

I cannot seem to solve this error with a stored procedure. The code works fine outside of the procedure but when I save it to the procedure I receive

Error "join_table" is not a known variable
Line 14: INTO join_table

If I remove that line everything works perfectly. Appreciate any suggestions or help. The idea is to have the procedure truncate the main 2 tables, drop and recreate the join_table based on a trigger to refresh all data.

CREATE OR REPLACE PROCEDURE rental_refresh()
LANGUAGE plpgsql
AS $$
BEGIN
TRUNCATE new_table;
TRUNCATE ts_30;
DROP TABLE join_table;

INSERT INTO new_table (c1, c2, c3,c4,c5)
SELECT c1, c2, c3, c4,c5
FROM other_table;

SELECT r.c1,r.c2,cr.c1,cr.c2,cr.c3,cr.c4,cr.sc5
INTO join_table
FROM new_table cr
INNER JOIN r_table r ON cr.c1=r.r1;
END; $$
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Meenan
  • 17
  • 1

1 Answers1

0

Use CREATE TABLE AS. Never use use SQL's SELECT INTO. Postgres supports it in SQL since it (unfortunately) still is part of standard SQL, but its use is officially discouraged. Read the notes in the manual.

In PL/pgSQL SELECT INTO is used to assign variables, hence your error message.

See:

That said, your PROCEDURE does not have to use PL/pgSQL. Could be plain SQL:

CREATE OR REPLACE PROCEDURE rental_refresh()
  LANGUAGE sql AS
$proc$
TRUNCATE new_table;
TRUNCATE ts_30;
DROP TABLE join_table;

INSERT INTO new_table (c1, c2, c3, c4, c5)
SELECT c1, c2, c3, c4,c5
FROM   other_table;

CREATE TABLE join_table AS
SELECT r.c1,r.c2,cr.c1,cr.c2,cr.c3,cr.c4,cr.sc5
FROM   new_table cr
JOIN   r_table r ON cr.c1 = r.r1;
$proc$

(But use CREATE TABLE AS in any case.)

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228