CODEWITHSUNDEEP
postgresqlpostgresql-9.1postgresql-9.2postgresql-9.3
Mojtaba Goudarzi
Mojtaba Goudarzi

Reputation: 9

Column doesn't exist in PostgreSQL View

I have the below situation but I don't get what is the problem with my code. I receive this error: column vx1 doesn't exist (vx1 is variable not column).

    vx1 double precision;
    vy1 double precision;
    vz1 double precision;
    vx2 double precision;
    vy2 double precision;
    vz2 double precision;
begin
    vx1 := x1;
    vy1 := y1;
    vz1 := z1;
    vx2 := x2;
    vy2 := y2;
    vz2 := z2;

    create view "shortestpathEdges" as
    select *
    from tbledges te
    where te.x<=vx1 and te.y<=vy1 and te.z<=vz1 and 
          te.x<=vx2 and te.y<=vy2 and te.z<=vz2;

this is the full function but it makes you feel not good so I made it very simple because still error is there.

create temp view shortestpathEdges as
select *
from(select x , y, z, vid   
     from(select tv.x as x,tv.y as y,tv.z as z,tv."VertexID" as vid
      from (select te."EdgeID" edgeid, te."VertexID" vertexid
        from tbledges te
        where te.status='dual')as t1, tblvertices as tv
      where t1.vertexid=tv."VertexID") as tv2
     where tv2.x<=vx1 and tv2.y<=vy1 and tv2.z<=vz1 and 
         tv2.x<=vx2 and tv2.y<=vy2 and tv2.z<=vz2) as tv3, tbledges as tble
where tv3.vid=tble."VertexID";

Upvotes: 0

Views: 229

Answers (1)

Robins Tharakan
Robins Tharakan

Reputation: 2473

The answer is (arguably cryptically) mentioned in the Variable Substitution section of the PL/Pgsql help document.

Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the utility statement as a string and EXECUTE it.

This means that variable substitution in PL/Pgsql doesn't happen on DDL commands (yet).

For this, you should use EXECUTE statement (the link has examples on how you could construct a variable sql_string that can be simply run via EXECUTE sql_string;)

Upvotes: 1

Related Questions