Re: Unfortunate expansion of composite types in union

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Pavel Stehule wrote:
> PostgreSQL doesn't support Common Table Expressions - you can write
> SRF function:
>
> CREATE OR REPLACE FUNCTION c(integer)
> RETURNS SETOF something AS $$
> DECLARE r RECORD;
> o something;
> BEGIN
> FOR r IN SELECT * FROM link WHERE link.s = $1 LOOP
> o := (1, r.id, NULL, NULL, NULL);
> RETURN NEXT o;
> o := (2, r.id, r.s, r.e, r.intensity, NULL);
> RETURN NEXT o;
> o := (3, r.id, r.o, r.format, NULL, r.data);
> RETURN NEXT o;
> RETURN;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT * FROM c(8692);
This is a completely different query from my one. (That is, the results are different.)

My problem is that I have a schema like
fastgraph=# \d object
Table "public.object"
Column | Type | Modifiers
- --------+--------+----------------------------------------------
id | bigint | not null default nextval('id_seq'::regclass)
Indexes:
"object_id_idx" UNIQUE, btree (id)

fastgraph=# \d link
Table "public.link"
Column | Type | Modifiers
- -----------+------------------+----------------------------------------------
id | bigint | not null default nextval('id_seq'::regclass)
s | bigint | not null
e | bigint | not null
intensity | double precision | not null
Indexes:
"link_id_idx" UNIQUE, btree (id)
"link_e_idx" btree (e)
"link_s_idx" btree (s)
"link_se_idx" btree (s, e)
Inherits: object

fastgraph=# \d representation
Table "public.representation"
Column | Type | Modifiers
- --------+--------+----------------------------------------------
id | bigint | not null default nextval('id_seq'::regclass)
o | bigint | not null
format | bigint | not null
data | bytea | not null
Indexes:
"representation_id_idx" UNIQUE, btree (id)
"representation_o_idx" btree (o)
"representation_text" hash (data) WHERE format = 1
Inherits: object

now I want those "objects" (with inheritance) which are connected to some other. So I tried the
query in the original post, and found the execution plan to be suboptimal. Today I tried to do it with OUTER JOINs
but failed utterly. So what is the best way to get the results? The original query is exactly what I need, only the plan
is bad. Any Ideas?

Regards,
Jens-Wolfhard Schicke

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHLfp+zhchXT4RR5ARAorgAKDr2grqWnxbvFMYOPiLJuHpjco30ACgswQB
9/qW9rz+ZngkBYdR0RLsils=
=LdBJ
-----END PGP SIGNATURE-----