Re: use of %TYPE

On Thu, 27 Feb 2003, Dennis Gearon wrote:

> Can I use %TYPE in a function definition a la:
> =============================================================================
> CREATE OR REPLACE FUNCTION add_usr ( Usrs.first_name%TYPE,
>                                      Usrs.middle_name%TYPE,
>                                      Usrs.sur_name%TYPE,
>                                      Usrs.sur_name_extra%TYPE,
>                                      Gens.gen%TYPE,
>                                      Emails.email%TYPE,
>                                      UsrEmailTypes.usr_email_type%TYPE )
> RETURNS BOOLEAN AS '
>     DECLARE
>     var_first_name      ALIAS FOR $1;
>     var_middle_name     ALIAS FOR $2;
>     var_sur_name        ALIAS FOR $3;
>     var_sur_name_extra  ALIAS FOR $4;
>     var_gen             ALIAS FOR $5;
>     var_email_addr      ALIAS FOR $6;
>     var_email_addr_type ALIAS FOR $7;
>     BEGIN
>         RETURN ''f'';
>     END;
> ' LANGUAGE 'plpgsql';
>
> =============================================================================

It works for me with current sources (after making tables with those
columns), although I believe the conversion is done immediately (given I
get notices about conversions to the column types), which would
presumably mean that it would not follow alter commands.

> Also, does pgsql keep case in the table and column names? phpPgAdmin
> shows no case on tables I created with case.

If you don't double quote the names they're case insensitive and in this
case forced to lower case.  If you double quote the name it keeps case and
is case sensitive, but you have to pretty much always refer to them with
the double quotes (unless the identifier was already in lower case).