Re: Need help with generic query

No idea on the function, but why not have a 'master' ticket table and
have the ones in each schema inherit from it? Then you could query
all tables by just querying the master table.

On Jun 20, 2007, at 5:55 AM, David Abrahams wrote:

> Background: I have a number of schemas all of which contain a "ticket"
> table having the same columns.  The goal of the function xticket1
> below is to collect all ticket rows satisfying some condition from all
> those schemas, and additionally label each one by adding a new column
> containing the name of the schema it belongs to.
>
>   -- Create a temporary table with the right layout
>   -- for our function's return type (know a better way?)
>   CREATE TEMP TABLE tix ( LIKE master.ticket );
>   ALTER TABLE tix ADD COLUMN schema_name text;
>
>   CREATE OR REPLACE FUNCTION xticket1(condition TEXT)
>      RETURNS SETOF tix
>      AS
>   $BODY$
>   DECLARE
>      scm RECORD;
>      result RECORD;
>   BEGIN
>      FOR scm IN SELECT schema_name FROM public.instance_defs LOOP
>        FOR result IN EXECUTE 'SELECT '
>            || scm.schema_name || '.ticket.*,'
>            || ' ''' || scm.schema_name || ''' AS schema_name'
>            || ' FROM ' || scm.schema_name || '.ticket'
>            || ' WHERE ' || condition
>            LOOP
>          RETURN NEXT result;
>        END LOOP;
>      END LOOP;
>      RETURN;
>   END;
>   $BODY$
>      LANGUAGE plpgsql;
>
>
> The problem is, psql is complaining:
>
>   ERROR:  wrong record type supplied in RETURN NEXT
>
> I don't know why, and I don't know how to get psql to give me useful
> debugging info that would help me discover why.  Can someone help?
>
> Thanks
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)