[postgis-users] IF, ELSE, FOR and WHILE in postgis

[P Kishor]

2009/10/12 Daniel Grum <daniel.grum at unibw.de>:
> Hi, I have a probleme with PostgreSQL and PostGIS.
>
> I need loops for the programming of a PostGIS SQL-script but PostgreSQL
>  makes truble.
>
> I think I know how I have to use loops in SQL.-->I need loops to update my
> table line by line!
>
> ELSE PostgreSQl said: "as an expression used underinquiry proved more than
> one line"
>
> The SQL-Skript:
> UPDATE einheiten_und_bewegungen
> SET the_geom = (SELECT
> ST_Line_SubString(ST_LineMerge(line.the_geom),(1-((Length(line.the_geom)-1000)
>           /Length(line.the_geom))),1)
>       FROM public.einheiten_und_bewegungen line
>       WHERE length(line.the_geom)>1000
>       ORDER BY line.gid);
>
> In the table einheiten_und_bewegungen are more than one line!

I am not sure what you are trying to do, but you are mixing metaphors
above. An UPDATE statement is made of

UPDATE <table>
SET <column_1> = ?, <column_2> = ?, .. <column_n> = ?
WHERE <condition>

If you update expression is a SELECT statement (a sub-select) then it
has to return one and only one value. So, the following

SELECT ST_Line_SubString(ST_LineMerge(line.the_geom),(1-((Length(line.the_geom)-1000)
          /Length(line.the_geom))),1)
      FROM public.einheiten_und_bewegungen line
      WHERE length(line.the_geom)>1000
      ORDER BY line.gid

won't work because it is likely to return more than one values. Think
of it this way... you can SET the_geom equal to only one geometry, no?
So, your sub-select has to return only one geometry.

Perhaps if you explained more clearly what you wanted to do, you would
get better help.






>
> If there are some ideas please help me!
>
> --daniel
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States