Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function .. AS..?

Function .. AS..?

From
"Ing. Jhon Carrillo"
Date:
Those instructions are good but i want  to call this function  only  for  " select  consulta_contacto(1)"  nothing more,  Is really necesary to use  "AS ..."?
 
this is  the call:
 
 
select * from  consulta_contacto(1) as (cont_codigo      integer,
                                        cont_idpers      varchar,
                                        cont_nombre      varchar,
                                        cont_apellido    varchar,
                                        cont_titulo      varchar,
                                        cont_fecnac      timestamp ,
                                        cont_codedociv   integer,
                                        cont_sexo        char(1),
                                        cont_codpais     integer,
                                        cont_pw          varchar,
                                        cont_empr        varchar,
                                        cont_cargo       varchar,
                                        cont_pwempr      varchar,
                                        cont_aniv        char(5),
                                        cont_prof        varchar,
                                        cont_trab        char(1),
                                        cont_fecgen      timestamp,
                                        cont_fecing      timestamp,
                                        cont_fuente      char(1),
                                        cont_sinc        char(1),
                                        cont_codupload   integer);
 
this is the function:
 

CREATE OR REPLACE FUNCTION consulta_contacto(integer) RETURNS SETOF RECORD AS '
DECLARE
   rec RECORD;
   sup INTEGER;
BEGIN
   FOR rec IN SELECT cont_codigo,cont_idpers, cont_nombre,cont_apellido,cont_titulo,cont_fecnac,cont_codedociv,cont_sexo,cont_codpais,cont_pw,cont_empr,cont_cargo,cont_pwempr,cont_aniv,cont_prof,cont_trab,cont_fecgen,cont_fecing,cont_fuente,cont_sinc,cont_codupload
      FROM tbu_contacto
     LOOP
       RETURN NEXT rec ;
     END LOOP;
   RETURN ;
END;
' LANGUAGE plpgsql;

Re: Function .. AS..?

From
Stephan Szabo
Date:
On Wed, 9 Feb 2005, Ing. Jhon Carrillo wrote:

> Those instructions are good but i want to call this function only for "
> select consulta_contacto(1)"  nothing more, Is really necesary to use
> "AS ..."?

If it absolutely needs to be setof record, yes.

It may be more appropriate to make a composite type with CREATE TYPE AS
and then make the function return SETOF <newtypename> instead.

Re: [SQL] Function .. AS..?

From
Michael Fuhr
Date:
On Wed, Feb 09, 2005 at 04:57:14PM -0400, Ing. Jhon Carrillo wrote:

> Those instructions are good but i want  to call this function only
> for " select  consulta_contacto(1)" nothing more, Is really necesary
> to use  "AS ..."?
> 
> this is  the call:
> 
> select * from  consulta_contacto(1) as (cont_codigo      integer, 
>                                         cont_idpers      varchar,
>                                         ...

Instead of declaring the function to return SETOF RECORD, you could
return SETOF typename, where "typename" is the name of a composite
type or a table; your queries could then omit "AS ...."

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/