Обсуждение: Need help with generic query

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

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)