CODEWITHSUNDEEP
postgresqlpostgiscreate-tableinsert-into
WhaleShark
WhaleShark

Reputation: 89

PostgreSQL select INTO function

I am writing a function which will select and SUM the resulting output into a new table-therefore I attempted to use the INTO function. However, my standalone code works, yet once a place into a function I get an error stating that the new SELECT INTO table is not a defined variable (perhaps I am missing something). Please see code below:

CREATE OR REPLACE FUNCTION rev_1.calculate_costing_layer()
  RETURNS trigger AS
$BODY$
BEGIN
   -- This will create an intersection between pipelines and sum the cost to a new table for output
   -- May need to create individual cost columns- Will also keep infrastructure costing seperated
   --DROP table rev_1.costing_layer;
   SELECT inyaninga_phases.geom, catchment_e_gravity_lines.name, SUM(catchment_e_gravity_lines.cost) AS gravity_sum
   INTO rev_1.costing_layer
   FROM rev_1.inyaninga_phases 
   ON ST_Intersects(catchment_e_gravity_lines.geom,inyaninga_phases.geom)
   GROUP BY catchment_e_gravity_lines.name, inyaninga_phases.geom;
  RETURN NEW;
END;
$BODY$
language plpgsql

Upvotes: 1

Views: 3699

Answers (2)

joanolo
joanolo

Reputation: 6328

Although SELECT ... INTO new_table is valid PostgreSQL, its use has been deprecated (or, at least, "unrecommended"). It doesn't work at all in PL/PGSQL, because INSERT INTO is used to get results into variables.

If you want to create a new table, you should use instead:

CREATE TABLE rev_1.costing_layer AS
SELECT 
    inyaninga_phases.geom, catchment_e_gravity_lines.name, SUM(catchment_e_gravity_lines.cost) AS gravity_sum
FROM 
    rev_1.inyaninga_phases 
    ON ST_Intersects(catchment_e_gravity_lines.geom,inyaninga_phases.geom)
GROUP BY 
    catchment_e_gravity_lines.name, inyaninga_phases.geom;

If the table has already been created an you just want to insert a new row in it, you should use:

INSERT INTO
     rev_1.costing_layer
     (geom, name, gravity_sum)
-- Same select than before
SELECT 
    inyaninga_phases.geom, catchment_e_gravity_lines.name, SUM(catchment_e_gravity_lines.cost) AS gravity_sum
FROM 
    rev_1.inyaninga_phases 
    ON ST_Intersects(catchment_e_gravity_lines.geom,inyaninga_phases.geom)
GROUP BY 
    catchment_e_gravity_lines.name, inyaninga_phases.geom;

In a trigger function, you're not very likely to create a new table every time, so, my guess is that you want to do the INSERT and not the CREATE TABLE ... AS.

Upvotes: 2

klin
klin

Reputation: 121604

Per the documentation:

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

Use CREATE TABLE AS.

Upvotes: 5

Related Questions