Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id CA00E9FB5FB
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 20 Mar 2007 00:41:20 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 18542-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 20 Mar 2007 00:41:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id 65C899FB2CE
	for <pgsql-sql@postgresql.org>; Tue, 20 Mar 2007 00:41:17 -0300 (ADT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id l2K3fB5t042406
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Mon, 19 Mar 2007 20:41:14 -0700 (MST)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.8/8.13.8) with ESMTP id l2K3fBeo023751;
	Mon, 19 Mar 2007 21:41:11 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.8/8.13.8/Submit) id l2K3fB95023750;
	Mon, 19 Mar 2007 21:41:11 -0600 (MDT) (envelope-from mfuhr)
Date: Mon, 19 Mar 2007 21:41:11 -0600
From: Michael Fuhr <mike@fuhr.org>
To: hu js <hu.bill2005@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to declare cursor if tablename is a variable?
Message-ID: <20070320034111.GA23662@winnie.fuhr.org>
Reply-To: pgsql-sql@postgresql.org
References: <20070314140934.GA7086@winnie.fuhr.org>
	<BAY102-F146D8632913581B7A9AE40F0750@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <BAY102-F146D8632913581B7A9AE40F0750@phx.gbl>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/264
X-Sequence-Number: 27902

[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