passing tables name into a cursor

From: John Kelly <jtkells(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: passing tables name into a cursor
Date: 2013-11-25 17:43:16
Message-ID: CAOM0cGr-UjzgQrPN4_0-tvh0V9boHbEJVzGYVTDQeu=Tk57iOg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Im having a problem changing several cursors in a function to use a passed
in child table name. Database is 8.4.3 on Linux
--Original cursor

c_runway18a cursor is

( select id, geom
, way_num as waydesignator, status_d as status
, t_width as width
, t_len as length
, coalesce(type_d,'X') as type
from v_features
where f_type = ' || feature_type and transaction_id = ' || id );

-- Modified cursor

SQL_18a text :='select id '
|| ' , geom '
|| ' , way_num as waydesignator '
|| ' , status_d as status '
|| ' , t_width as width '
|| ' , t_len as length '
|| ' , coalesce(type_d,''X'') as type '
|| ' from ' ||v_features_child
--the table is a child table, so I have to passed in to the function so
it will use indexes
|| ' where f_type = ' || feature_type
|| ' and transaction_id = ' || id ;

c_18a cursor is execute SQL_18a ;
-- this works on 9.1 Windows, but when I compile it on a 8.4.3 system I
get the following error

psql:val_ways.sql:756: ERROR: syntax error at or near ":"
LINE 1: execute $1
^
QUERY: execute $1
CONTEXT: SQL statement in PL/PgSQL function "val_ways" near line 26

I have tried "c_18a cursor is SQL_18a" without the execute clause, with
single quotes and ':' and get the same general error "QUERY: $1" .
Any thoughts?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Glyn Astill 2013-11-25 17:46:21 Re: Slony-I installation Help !
Previous Message Mahlon E. Smith 2013-11-25 17:02:52 Re: Streaming replication slave crash