Re: Dynamic plpgsql help

Hi,

instead of cast(mostype as varchar) try mostype::varchar

Thanks
Sreelatha

On Sun, Apr 4, 2010 at 10:04 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> 2010/4/4 Scott Geller <sgellergsu(at)gmail(dot)com>:
> > Hi
> >
> > I have the following plpgsql function that works:
> >
> > DROP TYPE if exists distr CASCADE;
> > CREATE TYPE distr AS (b bigint, var varchar);
> >
> > DROP FUNCTION IF EXISTS dist() cascade;
> >
> > CREATE or replace FUNCTION dist() RETURNS setof distr
> > AS $$ begin
> > return query EXECUTE 'Select count(*) as b, cast(mostype as varchar)
> > as var from segmentation group by mostype';
> > end $$ LANGUAGE 'plpgsql' VOLATILE;
> > ;
> >
> > select * from dist();
> >
> > When I try to make it dynamic, I get the error:
> >
> > ERROR: column "mostype" does not exist
> >
> > DROP TYPE if exists distr CASCADE;
> > CREATE TYPE distr AS (b bigint, var varchar);
> >
> > DROP FUNCTION IF EXISTS dist(text) cascade;
> >
> > CREATE or replace FUNCTION dist(text) RETURNS setof distr
> > AS $$ begin
> > return query execute 'Select count(*) as b, cast( ' ||
> > quote_ident($1) || ' as varchar) as var from segmentation group by '
> > || quote_ident($1);
> > end $$ LANGUAGE 'plpgsql' VOLATILE;
> > ;
> >
> > select dist(mostype);
>
> maybe select dist('mostype')
>
> regards
>
> Pavel Stehule
> >
> > Your help is appreciated
> >
> > Scott
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>