Thread: A View to Share

A View to Share

From
Date:
This is an a view I have found somewhat useful in helping partially
automate definition of functions during database design and testing:

CREATE VIEW public.function_elements AS
SELECT
 pg_catalog.pg_class.relname,
 pg_attribute.attname,
 typname,
 pg_attribute.attnum,
 cast('l_'||pg_attribute.attname as varchar) as local_variable_name,
 cast('l_'||pg_attribute.attname||' ALIAS FOR $'||btrim(TO_CHAR(attnum,
'999;')) as varchar) as local_alias_declaration,
 cast(pg_attribute.attname||' = l_'||pg_attribute.attname as varchar) as
equality_expression
FROM pg_attribute LEFT JOIN pg_type ON pg_type.oid =
pg_attribute.atttypid, pg_catalog.pg_class
WHERE pg_catalog.pg_attribute.attrelid = pg_class.oid
 AND pg_catalog.pg_attribute.attstattarget <> 0
ORDER BY
 pg_catalog.pg_class.relname,
 pg_catalog.pg_attribute.attnum


~Berend Tober




Re: A View to Share

From
"Jimmie H. Apsey"
Date:
I could not select from pg_catalog.  Is it called something else in
Postgres 7.1.3?
Postgres 7.1.3 is included in RED HAT ADVANCED SERVER 2.1 which I am using.
Jim Apsey
btober@seaworthysys.com wrote:

>This is an a view I have found somewhat useful in helping partially
>automate definition of functions during database design and testing:
>
>CREATE VIEW public.function_elements AS
>SELECT
> pg_catalog.pg_class.relname,
> pg_attribute.attname,
> typname,
> pg_attribute.attnum,
> cast('l_'||pg_attribute.attname as varchar) as local_variable_name,
> cast('l_'||pg_attribute.attname||' ALIAS FOR $'||btrim(TO_CHAR(attnum,
>'999;')) as varchar) as local_alias_declaration,
> cast(pg_attribute.attname||' = l_'||pg_attribute.attname as varchar) as
>equality_expression
>FROM pg_attribute LEFT JOIN pg_type ON pg_type.oid =
>pg_attribute.atttypid, pg_catalog.pg_class
>WHERE pg_catalog.pg_attribute.attrelid = pg_class.oid
> AND pg_catalog.pg_attribute.attstattarget <> 0
>ORDER BY
> pg_catalog.pg_class.relname,
> pg_catalog.pg_attribute.attnum
>
>
>~Berend Tober
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
>