Kindly help me to write a PostgreSQL PL/Python function doing the exact job as the following Python code. My problem is mainly the PL/Python syntax. Thanks.

import psycopg2 import osgeo.ogr connection = psycopg2.connect("host='localhost' dbname='my_db' user='postgres' password='pass123'") cursor1 = connection.cursor() cursor1.execute("SELECT id FROM table1") for i in cursor1: if i[0]>=100: print '+100' elif i[0]<100: print '-100' connection.commit() 

Let me rephrase my question. I am almost done. Just wanna know some codes indicated by the comments:

CREATE OR REPLACE FUNCTION test1() RETURNS setof varchar # I am not sure if this return type is correct AS $$ import psycopg2 import osgeo.ogr import math statement1 = "select count(*) from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM kn_ways_new_split_saveas', 350, 329, false, false) a LEFT JOIN kn_ways_new_split_saveas b ON (a.id2 = b.gid)" statement2 = "select seq, id1 AS node, id2 AS edge, cost, b.the_geom, b.source, b.target, b.slope_st_pt, b.slope_end_pt from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM kn_ways_new_split_saveas', 350, 329, false, false) a LEFT JOIN kn_ways_new_split_saveas b ON (a.id2 = b.gid)" count1 = plpy.execute(statement1) run2 = plpy.execute(statement2) len1 = count1[0]['count'] row1 = []; for i in range(len1):   if run2[i]['node']==run2[i]['target']:   # Here I want to append my row1 variable with corresponding row's node, edge, the_geom etc.  elif run2[i]['node']==run2[i]['source']:   # Here I want to append my row1 variable with corresponding row's node, edge, the_geom etc. return row1; # Here return the final table row1 just created by above for loop $$ LANGUAGE 'plpythonu' VOLATILE;  

I am using OSM data to run pgr_dijkstra() for weighted-path calculation. But after importing OSM data using osm2pgsql I populated some additional columns into my ways table. So now it has gid serial, the_geom geometry, source integer, target integer, source_height double precision, target_height double precision plus some other columns. The source_height and target_height columns I have populated using the DEM of the region.

Simply running the pgr_dijkstra() on ways will give seq, id1 (node), id2 (edge), cost columns. What I want is to also determine the source_height or target_height for a particular edge if the node is equal to the source or target. I was trying to write a procedural function using PL/Python (although I don't know which procedural language is better) but couldn't complete.

Following is the attempt to create a PL/Python function. The problem is I don't know how to append all the rows coming from the for loop into a variable to return it as a table.

CREATE OR REPLACE FUNCTION test1(source int, target int, tablename text) RETURNS setof varchar # I am not sure if this return type is correct AS  $$ import psycopg2 import osgeo.ogr statement1 = ("select count(*) from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM %s', %s, %s, false, false) a LEFT JOIN %s b ON (a.id2 = b.gid)" % (tablename, source, target, tablename)) statement2 = ("select seq, id1 AS node, id2 AS edge, cost, b.the_geom, b.source, b.target, b.slope_st_pt, b.slope_end_pt from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM %s', %s, %s, false, false) a LEFT JOIN %s b ON (a.id2 = b.gid)" % (tablename, source, target, tablename)) count1 = plpy.execute(statement1) run2 = plpy.execute(statement2) len1 = count1[0]['count'] row1 = []; for i in range(len1): if run2[i]['node']==run2[i]['source']: # Code to append node, edge, the_geom and slope_st_pt as slope to row1 elif run2[i]['node']==run2[i]['target']: # Code to append node, edge, the_geom and slope_end_pt as slope to row1 return row1; # Return row1 table just created using the for loop above $$  LANGUAGE 'plpythonu' VOLATILE; 

Kindly help me to write a PostgreSQL PL/Python function doing the exact job as the following Python code. My problem is mainly the PL/Python syntax. Thanks.

import psycopg2 import osgeo.ogr connection = psycopg2.connect("host='localhost' dbname='my_db' user='postgres' password='pass123'") cursor1 = connection.cursor() cursor1.execute("SELECT id FROM table1") for i in cursor1: if i[0]>=100: print '+100' elif i[0]<100: print '-100' connection.commit() 

Kindly help me to write a PostgreSQL PL/Python function doing the exact job as the following Python code. My problem is mainly the PL/Python syntax. Thanks.

import psycopg2 import osgeo.ogr connection = psycopg2.connect("host='localhost' dbname='my_db' user='postgres' password='pass123'") cursor1 = connection.cursor() cursor1.execute("SELECT id FROM table1") for i in cursor1: if i[0]>=100: print '+100' elif i[0]<100: print '-100' connection.commit() 

Let me rephrase my question. I am almost done. Just wanna know some codes indicated by the comments:

CREATE OR REPLACE FUNCTION test1() RETURNS setof varchar # I am not sure if this return type is correct AS $$ import psycopg2 import osgeo.ogr import math statement1 = "select count(*) from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM kn_ways_new_split_saveas', 350, 329, false, false) a LEFT JOIN kn_ways_new_split_saveas b ON (a.id2 = b.gid)" statement2 = "select seq, id1 AS node, id2 AS edge, cost, b.the_geom, b.source, b.target, b.slope_st_pt, b.slope_end_pt from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM kn_ways_new_split_saveas', 350, 329, false, false) a LEFT JOIN kn_ways_new_split_saveas b ON (a.id2 = b.gid)" count1 = plpy.execute(statement1) run2 = plpy.execute(statement2) len1 = count1[0]['count'] row1 = []; for i in range(len1): if run2[i]['node']==run2[i]['target']: # Here I want to append my row1 variable with corresponding row's node, edge, the_geom etc. elif run2[i]['node']==run2[i]['source']: # Here I want to append my row1 variable with corresponding row's node, edge, the_geom etc. return row1; # Here return the final table row1 just created by above for loop $$ LANGUAGE 'plpythonu' VOLATILE;