Forum OpenACS Q&A: %TYPE on views in PG

Collapse
Posted by Pascal Scheffers on
It seems that %TYPE does not work on view columns, is this a bug or (missing) feature in PG 7.1.2? I could not find %TYPE in the PG documentation, so... does anybody know?

It is used, for example, in acs-messaging the function:

create function acs_message__name (integer)
returns varchar as '
declare
    p_message_id   alias for $1;
    v_message_name  acs_messages_all.title%TYPE;
begin
    select title into v_message_name
        from acs_messages_all
        where message_id = p_message_id;
    return v_message_name;
end;' language 'plpgsql';
The above code does not work, but when I substitute acs_messages_all.title with cr_revisions.title it works just fine.

Collapse
Posted by Don Baccus on
I'd report this to Jan Wieck, who wrote PL/pgSQL, and ask if it's a bug that needs fixing or something that shouldn't work.

Views in PG are implemented using the rule system, so implementing this feature could be tricky.  Jan would know, though.

For now, obviously, you have to work around it regardless of whether or not Jan thinks this should and can be made to work.