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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)