X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D36799F944D for ; Sun, 14 May 2006 13:17:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 98429-08 for ; Sun, 14 May 2006 13:16:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.230]) by postgresql.org (Postfix) with ESMTP id 961F89F9C18 for ; Sun, 14 May 2006 13:16:59 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 57so638149wri for ; Sun, 14 May 2006 09:16:58 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=izACx+J22E5l2E8a485qbK8zdterbF9kO2tH5lZckcV4/WWlVIJH+7gJZrOws6Q88hCz3GhReL8CCaTMk1Dy/lx7KZASKSkc73/p7o0Jx5WUOF3He+SUxQfLfIUypU4dKNRAjnLzD4l4JmxLC0aAYRTD2dZzQA4T33GG299SNo4= Received: by 10.54.100.8 with SMTP id x8mr2310973wrb; Sun, 14 May 2006 09:16:58 -0700 (PDT) Received: by 10.54.98.9 with HTTP; Sun, 14 May 2006 09:16:58 -0700 (PDT) Message-ID: Date: Sun, 14 May 2006 11:16:58 -0500 From: "Jaime Casanova" To: "Scott Yohonn" Subject: Re: PL/PGSQL - How to pass in variables? Cc: "pgsql-sql@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/107 X-Sequence-Number: 25138 On 5/14/06, Scott Yohonn wrote: > > Using PL/PGSQL, I am trying to create a procedure to display the > count of rows in any single table of a database. The End-user would > pass in a table name and the prodecure would display the table name > with the row count. > I am able to hardcode the variable for table and get the appropriate > results from my count function (see below), but cannot pass in a > variable and have the function work. Any suggesstions??? > > CREATE FUNCTION get_table_count(tablename text) RETURNS integer AS $$ > DECLARE > > --tablename ALIAS FOR $1; > > rowcount INTEGER; > BEGIN > > SELECT INTO rowcount count(*) FROM tablename; > > RETURN rowcount; > > END; > $$ LANGUAGE 'plpgsql'; > you can't do this because tablename is a variable not a table, you have to append the content of the variable in a string that can be EXECUTE'd EXECUTE 'SELECT count(*) FROM ' || tablename INTO rowcount; --=20 regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook