Thread: Function problem

Howdy all,

I have a function that worked as regular SQL before I tried to make it a
function.  I suspect it has something to do with the literal strings I'm
looking at, but I can't figure it out.  every time I run the function, I
get an error message on the word TEMP.  I've tried removing the word temp
but then the error jumps to crossYTD.   I've tried making the literals
single quotes, but that doesn't seem to work.  Any suggestions?  TIA.

Create Function sp_ddw_crosstab() RETURNS int4 AS '

BEGIN

     /* Create the cross tab of YTD data */
     Select Item_UPC, Cat_Desc,Pat_Desc,
     Sum( Case Appl_ID When "NET" Then RESERVEDUNITS1 Else 0 End) as
Loc128_Demand,
     Sum( Case Appl_ID When "NET" Then RESERVEDDOLLARS Else 0 End) as
Loc128_DemandDollar
     into  TEMP crossytd
     From salesYTD q
     Group by Item_UPC,Cat_Desc,Pat_Desc;
     return 1;

END;
'LANGUAGE 'plpgsql';


Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office
HatcherPT - AIM

This a snippet of a much longer procedure.  I am creating a cross-tab of
sales data and dumping it into a temp table.  A bunch of other temp tables
are created and then the whole thing is put together in 1 table for
reporting purposes.  As stated earlier, it works if I don't put it into a
function and I think it has to do with my quotes (").

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office
HatcherPT - AIM





                    "Josh Berkus"
                    <agliodbs@openo       To:     "Patrick Hatcher" <PHatcher@macys.com>,
                    ffice.org>             pgsql-novice@postgresql.org
                                          cc:
                    03/15/2002            Subject:     Re: [NOVICE] Function problem
                    06:00 PM






Patrick,

Frankly, your syntax is wierd enough that I can't figure out what
 you're trying to do. Can you explain?

> Create Function sp_ddw_crosstab() RETURNS int4 AS '
>
> BEGIN
>
>      /* Create the cross tab of YTD data */
>      Select Item_UPC, Cat_Desc,Pat_Desc,
>      Sum( Case Appl_ID When "NET" Then RESERVEDUNITS1 Else 0 End) as
> Loc128_Demand,
>      Sum( Case Appl_ID When "NET" Then RESERVEDDOLLARS Else 0 End) as
> Loc128_DemandDollar
>      into  TEMP crossytd
>      From salesYTD q
>      Group by Item_UPC,Cat_Desc,Pat_Desc;
>      return 1;
>
> END;
> 'LANGUAGE 'plpgsql';

-Josh

Partrick,

> This a snippet of a much longer procedure.  I am creating a cross-tab
>  of
> sales data and dumping it into a temp table.  A bunch of other temp
>  tables
> are created and then the whole thing is put together in 1 table for
> reporting purposes.  As stated earlier, it works if I don't put it
>  into a
> function and I think it has to do with my quotes (").

No, the problem is that SELECT INTO in a procedure is for selecting
 data into variables.  PL/pgSQL is expecting a variable named "TEMP"
 and not finding one.  Instead, how about using the more standard
 CREATE TABLE AS SELECT ... ?

-Josh Berkus

Ahhhhhhhh.  Thank you Josh.  I just learned something new.  I've been doing
it this way in MS SQL for years.

Patrick Hatcher
Macys.Com





                    "Josh Berkus"
                    <josh@agliodbs       To:     "Patrick Hatcher" <PHatcher@macys.com>
                    .com>                cc:     pgsql-novice@postgresql.org
                                         Subject:     Re: [NOVICE] Function problem
                    03/15/2002
                    06:49 PM






Partrick,

> This a snippet of a much longer procedure.  I am creating a cross-tab
>  of
> sales data and dumping it into a temp table.  A bunch of other temp
>  tables
> are created and then the whole thing is put together in 1 table for
> reporting purposes.  As stated earlier, it works if I don't put it
>  into a
> function and I think it has to do with my quotes (").

No, the problem is that SELECT INTO in a procedure is for selecting
 data into variables.  PL/pgSQL is expecting a variable named "TEMP"
 and not finding one.  Instead, how about using the more standard
 CREATE TABLE AS SELECT ... ?

-Josh Berkus

Partick,

> Ahhhhhhhh.  Thank you Josh.  I just learned something new.  I've been
>  doing
> it this way in MS SQL for years.

Different Procedural SQL implementation, different syntax.

Transact-SQL:
variable assignment:
SELECT @fig=col1, @mike=col2
FROM table1 WHERE col3 = 'a'
temp table creation:
SELECT col1, col2 INTO temp_1
FROM table1 WHRE col3 = 'a'

PL/pgSQL:
variable assignment:
SELECT col1, col2 INTO v_fig, v_mike
FROM table1 WHERE col3='a';
temp table creation
CREATE TEMPORARY TABLE temp_1 AS
SELECT col1, col2 FROM table1
WHERE col3='a';

BTW, in either language you want to minimize your creation of temporary
 tables in procedures -- they are much slower than variables or even
 very complex queries.

-Josh Berkus