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).