Thread: passing tables name into a cursor

On Mon, Nov 25, 2013 at 11:43 AM, John Kelly <jtkells@gmail.com> wrote:
> 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?


Problem is in the EXECUTE statement, not in the query being executed.
My guess is you are relying on some feature that is not present in
8.4.  Can we see the complete execute statement?

merlin