pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to declare cursor if tablename is a variable?
4+ messages / 2 participants
[nested] [flat]
* How to declare cursor if tablename is a variable?
@ 2007-03-14 08:14 hu js <hu.bill2005@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: hu js @ 2007-03-14 08:14 UTC (permalink / raw)
To: pgsql-sql
See this:
“CREATE or replace function geo_polygon(tablename varchar) RETURNS integer
AS $$
DECLARE
objectid varchar;
tab varchar;
x1 float;
y1 float;
obj_num integer;
point_num integer;
polygonstr varchar;
cur_obj CURSOR FOR SELECT mrc_xy_position FROM tablename;
cur_point CURSOR FOR SELECT x,y FROM tablename where mrc_xy_position =
objectid;
BEGIN
…….“
Error if call the function. Neither to use “execute” . Who can solve it?
Thanks!
_________________________________________________________________
享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to declare cursor if tablename is a variable?
@ 2007-03-14 14:09 Michael Fuhr <mike@fuhr.org>
parent: hu js <hu.bill2005@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2007-03-14 14:09 UTC (permalink / raw)
To: hu js <hu.bill2005@hotmail.com>; +Cc: pgsql-sql
On Wed, Mar 14, 2007 at 08:14:07AM +0000, hu js wrote:
> CREATE or replace function geo_polygon(tablename varchar) RETURNS
> [...]
> cur_obj CURSOR FOR SELECT mrc_xy_position FROM tablename;
> cur_point CURSOR FOR SELECT x,y FROM tablename where mrc_xy_position =
> objectid;
>
> Error if call the function. Neither to use “execute” . Who can solve
> it? Thanks!
Are you sure you need to use cursors? Can you not build the query
strings and use EXECUTE or "FOR variable IN EXECUTE query LOOP"?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to declare cursor if tablename is a variable?
@ 2007-03-20 03:41 Michael Fuhr <mike@fuhr.org>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2007-03-20 03:41 UTC (permalink / raw)
To: hu js <hu.bill2005@hotmail.com>; +Cc: pgsql-sql
[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]
On Tue, Mar 20, 2007 at 02:28:15AM +0000, hu js wrote:
> It doesn't work. Because can't use variable for table name in query.
>
> Please tell me another way. Thanks
What exactly have you tried that doesn't work? Will neither of the
following do what you want? What version of PostgreSQL are you using?
DECLARE
rec_obj record;
query_obj text := 'SELECT mrc_xy_position FROM ' || quote_ident(tablename);
BEGIN
FOR rec_obj IN EXECUTE query_obj LOOP
[...]
END LOOP;
or
DECLARE
cur_obj refcursor;
query_obj text := 'SELECT mrc_xy_position FROM ' || quote_ident(tablename);
BEGIN
OPEN cur_obj FOR EXECUTE query_obj;
[...]
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to declare cursor if tablename is a variable?
@ 2007-03-23 11:47 Michael Fuhr <mike@fuhr.org>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2007-03-23 11:47 UTC (permalink / raw)
To: hu js <hu.bill2005@hotmail.com>; +Cc: pgsql-sql
On Wed, Mar 21, 2007 at 02:20:21AM +0000, hu js wrote:
> But I get into another trouble.See following program.
As I requested before, please post questions to the mailing list
so others can participate in and learn from the discussion.
> "query_xypos" is ok. but "query_xydata" is not ok.
> I find "objectid" not quoted corrected.It seems relevant with variable
> varchar and "'".
[...]
> query_xypos text := 'SELECT mrc_xy_position FROM ' ||
> quote_ident(tablename);
> query_xydata text := 'SELECT x,y FROM ' || tablename || ' where
> mrc_xy_position = ' || quote_ident(objectid) ;
See the documentation to learn more about quote_ident() and
quote_literal() and when to use each:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-...
http://www.postgresql.org/docs/8.2/interactive/functions-string.html#FUNCTIONS-STRING-OTHER
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-03-23 11:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-14 08:14 How to declare cursor if tablename is a variable? hu js <hu.bill2005@hotmail.com>
2007-03-14 14:09 ` Michael Fuhr <mike@fuhr.org>
2007-03-20 03:41 ` Michael Fuhr <mike@fuhr.org>
2007-03-23 11:47 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox