pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Create table in functions
10+ messages / 6 participants
[nested] [flat]

* Create table in functions
@ 2000-08-23 14:49 Andreas Tille <tillea@rki.de>
  2000-08-23 15:31 ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-23 16:46 ` Re: Create table in functions Jie Liang <jliang@ipinc.com>
  2000-08-24 09:36 ` Re: Create table in functions hlefebvre <hlefebvre@lexbase.net>
  0 siblings, 3 replies; 10+ messages in thread

From: Andreas Tille @ 2000-08-23 14:49 UTC (permalink / raw)
  To: PostgreSQL SQL <pgsql-sql@hub.org>

Hello,

I striped down my function up to a strange thing:

web=# create function pHelpTable( )
web-#   returns int
web-#   As '
web'#     Begin
web'#       Create Table Temp_NumTable  ( Num int ) ;
web'#   
web'#       return 17 ;
web'#     End; '
web-#   language 'plpgsql' ;
CREATE
web=# 
web=# select pHelpTable( );
ERROR:  copyObject: don't know how to copy 611
web=# 


What does this mean?  The ERROR is caused by the Create Table statement
(when I removed it from my complex function it worked well).
So why doesn't this work and what copy function fails here??

Kind regards

          Andreas.




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
@ 2000-08-23 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-24 00:57   ` RE: Create table in functions Hiroshi Inoue <Inoue@tpf.co.jp>
  2 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2000-08-23 15:31 UTC (permalink / raw)
  To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>

Andreas Tille <tillea@rki.de> writes:
> web=# create function pHelpTable( )
> web-#   returns int
> web-#   As '
> web'#     Begin
> web'#       Create Table Temp_NumTable  ( Num int ) ;
> web'#   
> web'#       return 17 ;
> web'#     End; '
> web-#   language 'plpgsql' ;
> CREATE

The majority of utility commands don't work in plpgsql functions,
because no one had gotten 'round to writing querytree copy routines
for them.  Ian Turner finally did all the gruntwork for that a few
weeks ago, so this does work in current sources and will be in 7.1.

I believe you could make this work in 7.0 by using an SQL function
instead of plpgsql, if that helps any...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* RE: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
  2000-08-23 15:31 ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-08-24 00:57   ` Hiroshi Inoue <Inoue@tpf.co.jp>
  2000-08-24 05:32     ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-25 07:32     ` RE: Create table in functions Andreas Tille <tillea@rki.de>
  0 siblings, 2 replies; 10+ messages in thread

From: Hiroshi Inoue @ 2000-08-24 00:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>

> -----Original Message-----
> From: Tom Lane
> 
> Andreas Tille <tillea@rki.de> writes:
> > web=# create function pHelpTable( )
> > web-#   returns int
> > web-#   As '
> > web'#     Begin
> > web'#       Create Table Temp_NumTable  ( Num int ) ;
> > web'#   
> > web'#       return 17 ;
> > web'#     End; '
> > web-#   language 'plpgsql' ;
> > CREATE
> 
> The majority of utility commands don't work in plpgsql functions,
> because no one had gotten 'round to writing querytree copy routines
> for them.  Ian Turner finally did all the gruntwork for that a few
> weeks ago, so this does work in current sources and will be in 7.1.
>

Hmm,Andreas's original function seems to contain other statements.
If the function contains DML statements for the table Temp_Num_Table,
it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
  2000-08-23 15:31 ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-24 00:57   ` RE: Create table in functions Hiroshi Inoue <Inoue@tpf.co.jp>
@ 2000-08-24 05:32     ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-24 06:14       ` RE: Create table in functions Hiroshi Inoue <Inoue@tpf.co.jp>
  1 sibling, 1 reply; 10+ messages in thread

From: Tom Lane @ 2000-08-24 05:32 UTC (permalink / raw)
  To: Hiroshi Inoue <Inoue@tpf.co.jp>; +Cc: Andreas Tille <tillea@rki.de>; PostgreSQL SQL <pgsql-sql@hub.org>

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Hmm,Andreas's original function seems to contain other statements.
> If the function contains DML statements for the table Temp_Num_Table,
> it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.

Are you thinking about plpgsql's caching of query plans (and
specifically the table OIDs stored in those plans) or is there another
issue here?

We do need to think about invalidating cached query plans when updates
happen...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* RE: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
  2000-08-23 15:31 ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-24 00:57   ` RE: Create table in functions Hiroshi Inoue <Inoue@tpf.co.jp>
  2000-08-24 05:32     ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-08-24 06:14       ` Hiroshi Inoue <Inoue@tpf.co.jp>
  0 siblings, 0 replies; 10+ messages in thread

From: Hiroshi Inoue @ 2000-08-24 06:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andreas Tille <tillea@rki.de>; PostgreSQL SQL <pgsql-sql@hub.org>

> -----Original Message-----
> From: Tom Lane
>
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > Hmm,Andreas's original function seems to contain other statements.
> > If the function contains DML statements for the table Temp_Num_Table,
> > it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
>
> Are you thinking about plpgsql's caching of query plans (and
> specifically the table OIDs stored in those plans)

Yes.
We can create a table and insert into it in plpgsql functions if we
call such functions once per session(connection).
Would it be the spec of plpgsql functions in 7.1 ?

Regards.

Hiroshi Inoue




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* RE: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
  2000-08-23 15:31 ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-24 00:57   ` RE: Create table in functions Hiroshi Inoue <Inoue@tpf.co.jp>
@ 2000-08-25 07:32     ` Andreas Tille <tillea@rki.de>
  2000-09-01 02:36       ` Re: RE: Create table in functions Jan Wieck <janwieck@Yahoo.com>
  1 sibling, 1 reply; 10+ messages in thread

From: Andreas Tille @ 2000-08-25 07:32 UTC (permalink / raw)
  To: ; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>

On Thu, 24 Aug 2000, Hiroshi Inoue wrote:

> Hmm,Andreas's original function seems to contain other statements.
> If the function contains DML statements for the table Temp_Num_Table,
> it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
That's the problem.  I'm in the process of porting a set of about
30 Stroed Procedures from MS-SQL server to PostgreSQL and have
just managed only 3 :-( because of some "This is not possible with
PostgreSQL".

To make clear what I want to do, I just copy the original stored
procedure, which might be not so clever so that it would be possible
to go without the table creation:


CREATE Procedure TilleA.pHelpCountNames
( @Arbeitsgruppeshort varchar(255) ,
  @Condition          varchar(255) 
)
/* Count names in table Mitarbeiter which belong to the group
   Arbeitsgruppeshort and match the condition @Condition
*/
As
  Declare @Query varchar(1024)
  Declare @num int
  Select  @num = 0
  
  if @ArbeitsgruppeShort is NULL or @ArbeitsGruppeShort = '' begin
    Select @ArbeitsgruppeShort = ''
  end else begin
    Select @ArbeitsgruppeShort = ' AND a.ArbeitsgruppeShort = ' + '''' + @ArbeitsgruppeShort + ''''
  end

  Create Table #NumTable
  (
	Num integer
  )
  	
  Select @Query = 
         'Insert Into #NumTable (Num) ' +
         'SELECT Count (*) ' +
         'FROM Mitarbeiter m ' +
         'INNER JOIN tm2nMitarbeiterArbeitsgruppe t ON m.IdMitarbeiter   = t.IdMitarbeiter ' +
         'INNER JOIN Arbeitsgruppe a                ON t.IdArbeitsgruppe = a.IdArbeitsgruppe ' +
         'WHERE ' + @Condition + @ArbeitsgruppeShort
  Exec (@Query)

  Select @num=(Select Max(Num) From #NumTable)

  Drop Table #NumTable
  
  return @num


May be there are some clever ways to avoid the temporary table.
I really wonder if my solution is in fact very clever because I'm unsure
if it is thread-safe.

Any hints?

Kind regards

          Andreas.




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: RE: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
  2000-08-23 15:31 ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-24 00:57   ` RE: Create table in functions Hiroshi Inoue <Inoue@tpf.co.jp>
  2000-08-25 07:32     ` RE: Create table in functions Andreas Tille <tillea@rki.de>
@ 2000-09-01 02:36       ` Jan Wieck <janwieck@Yahoo.com>
  2000-09-01 12:44         ` Re: RE: Create table in functions Andreas Tille <tillea@rki.de>
  0 siblings, 1 reply; 10+ messages in thread

From: Jan Wieck @ 2000-09-01 02:36 UTC (permalink / raw)
  To: Andreas Tille <tillea@rki.de>; +Cc: pgsql-sql

Andreas Tille wrote:
> On Thu, 24 Aug 2000, Hiroshi Inoue wrote:
>
> > Hmm,Andreas's original function seems to contain other statements.
> > If the function contains DML statements for the table Temp_Num_Table,
> > it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
> That's the problem.  I'm in the process of porting a set of about
> 30 Stroed Procedures from MS-SQL server to PostgreSQL and have
> just managed only 3 :-( because of some "This is not possible with
> PostgreSQL".

    Could you try out the current CVS sources?

    I've  added  an  EXECUTE  keyword  to PL/pgSQL earlier - er -
    yesterday.  It should give you the power to at least port the
    proc below to PostgreSQL.

> To make clear what I want to do, I just copy the original stored
> procedure, which might be not so clever so that it would be possible
> to go without the table creation:

    Looks  like  the  only purpose of the (temp) table is to hold
    exactly one single row of one attribute - the result  of  the
    count(*).   From  the  code I guess that MS-SQL's only way to
    build up a dynamic query is to put it  as  a  string  into  a
    variable and call Exec(@var).  Maybe it's impossible to get a
    return value out of that  at  the  same  time,  so  the  only
    solution  is  to  throw  it  into a temp table and pick it up
    again.

    But anyway, the dynamic part of  building  the  qualification
    (WHERE  clause)  was  the initial problem. And that should be
    solved in CURRENT PostgreSQL sources (and  get  shipped  with
    7.1).

    Read on below...

>
>
> CREATE Procedure TilleA.pHelpCountNames
> ( @Arbeitsgruppeshort varchar(255) ,
>   @Condition          varchar(255)
> )
> /* Count names in table Mitarbeiter which belong to the group
>    Arbeitsgruppeshort and match the condition @Condition
> */
> As
>   Declare @Query varchar(1024)
>   Declare @num int
>   Select  @num = 0
>
>   if @ArbeitsgruppeShort is NULL or @ArbeitsGruppeShort = '' begin
>     Select @ArbeitsgruppeShort = ''
>   end else begin
>     Select @ArbeitsgruppeShort = ' AND a.ArbeitsgruppeShort = ' + '''' + @ArbeitsgruppeShort + ''''
>   end
>
>   Create Table #NumTable
>   (
>    Num integer
>   )
>
>   Select @Query =
>          'Insert Into #NumTable (Num) ' +
>          'SELECT Count (*) ' +
>          'FROM Mitarbeiter m ' +
>          'INNER JOIN tm2nMitarbeiterArbeitsgruppe t ON m.IdMitarbeiter   = t.IdMitarbeiter ' +
>          'INNER JOIN Arbeitsgruppe a                ON t.IdArbeitsgruppe = a.IdArbeitsgruppe ' +
>          'WHERE ' + @Condition + @ArbeitsgruppeShort
>   Exec (@Query)
>
>   Select @num=(Select Max(Num) From #NumTable)
>
>   Drop Table #NumTable
>
>   return @num
>
>
> May be there are some clever ways to avoid the temporary table.
> I really wonder if my solution is in fact very clever because I'm unsure
> if it is thread-safe.
>
> Any hints?

    Let me give it a try:

        CREATE FUNCTION pHelpCountNames (text, text) RETURNS integer AS
          '
            DECLARE
              ag_short      ALIAS FOR $1;
              condition     ALIAS FOR $2;
              query         text;
              rec           record;
              result        integer;
            BEGIN
              query := ''SELECT count(*) AS n FROM Mitarbeiter m,
                                tm2nMistarbeiterArbeitsgruppe t,
                                Arbeitsgruppe a
                         WHERE m.IdMitarbeiter = t.IdMitarbeiter
                           AND t.IdArbeitsgruppe = a.IdArbeitsgruppe'';

              IF condition != '''' THEN
                query := query || '' AND '' || condition;
              END IF;

              IF ag_short != '''' THEN
                query := query || '' AND a.ArbeitsgruppeShort = '''''' ||
                         ag_short || '''''''';
              END IF;

              FOR rec IN EXECUTE query LOOP
                result = rec.n;
              END LOOP;

              RETURN result;
            END;
          ' LANGUAGE 'plpgsql';

    Just written down. Too lazy to test it right now.

    BTW:   It   is  not  strictly  neccessary  to  build  up  the
    querystring  for  EXECUTE  in  a  variable.  Any   expression
    resulting  in a string (a function call or a concatenation of
    strings, variables and functions) is allowed.

    EXECUTE outside of a FOR statement simply executes the  query
    and  that  should  not  be  a  SELECT.  This can be a utility
    statement as well, so feel free to  create  and  drop  "temp"
    tables as needed. Used as above (inside the FOR) it must be a
    SELECT statement. I haven't checked if  rows  found,  because
    that  count(*)  query is guaranteed to return either one row,
    or bomb out with a transaction abort. Usually someone  should
    check.

    Enjoy.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: RE: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
  2000-08-23 15:31 ` Re: Create table in functions Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-24 00:57   ` RE: Create table in functions Hiroshi Inoue <Inoue@tpf.co.jp>
  2000-08-25 07:32     ` RE: Create table in functions Andreas Tille <tillea@rki.de>
  2000-09-01 02:36       ` Re: RE: Create table in functions Jan Wieck <janwieck@Yahoo.com>
@ 2000-09-01 12:44         ` Andreas Tille <tillea@rki.de>
  0 siblings, 0 replies; 10+ messages in thread

From: Andreas Tille @ 2000-09-01 12:44 UTC (permalink / raw)
  To: Jan Wieck <janwieck@Yahoo.com>; +Cc: pgsql-sql

On Thu, 31 Aug 2000, Jan Wieck wrote:

First of all thank you very much for your effort!
This is definitely a good argument for PostgeSQL to have such fast
support in adding features which might convince some of my collegues
or my boss :).

>     Could you try out the current CVS sources?
Well, I walked around the problem and I'm affrais I don't want to use
the CVS tree in a production environment.  Moreover I have very poor
bandwith and I'm sitting behind a CVS disabled firewall :(.  So I
have problems to check it soon.  May be I coudl try a patch against
7.0.2 sources if this would not make much trouble.
I definitely will honor your effort by testing it but I can't do this
in the next weeks!

>     I've  added  an  EXECUTE  keyword  to PL/pgSQL earlier - er -
>     yesterday.  It should give you the power to at least port the
>     proc below to PostgreSQL.
Hopefully some other PostgreSQL users besides me will love you for that .

Kind regards

        Andreas.




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
@ 2000-08-23 16:46 ` Jie Liang <jliang@ipinc.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Jie Liang @ 2000-08-23 16:46 UTC (permalink / raw)
  To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>

Hi, there,

I don't think that you can use DDL in PL/pgSQL, normally say , you can
only use DML in PL/pgSQL.
i.e. you can use select into,update,delete,insert ... , but you cannot use
create, alter, grant ...


Andreas Tille wrote:

> Hello,
>
> I striped down my function up to a strange thing:
>
> web=# create function pHelpTable( )
> web-#   returns int
> web-#   As '
> web'#     Begin
> web'#       Create Table Temp_NumTable  ( Num int ) ;
> web'#
> web'#       return 17 ;
> web'#     End; '
> web-#   language 'plpgsql' ;
> CREATE
> web=#
> web=# select pHelpTable( );
> ERROR:  copyObject: don't know how to copy 611
> web=#
>
> What does this mean?  The ERROR is caused by the Create Table statement
> (when I removed it from my complex function it worked well).
> So why doesn't this work and what copy function fails here??
>
> Kind regards
>
>           Andreas.

--
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Create table in functions
  2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
@ 2000-08-24 09:36 ` hlefebvre <hlefebvre@lexbase.net>
  2 siblings, 0 replies; 10+ messages in thread

From: hlefebvre @ 2000-08-24 09:36 UTC (permalink / raw)
  To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>

Andreas Tille wrote:
> What does this mean?  The ERROR is caused by the Create Table statement
> (when I removed it from my complex function it worked well).
> So why doesn't this work and what copy function fails here??

Maybe you can create your table using a select into statement :

something like :
SELECT int4(1) as Num INTO Temp_NumTable WHERE 1=2;

Should create your table without CREATE statement.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2000-09-01 12:44 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
2000-08-23 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-24 00:57   ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-08-24 05:32     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-24 06:14       ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-08-25 07:32     ` Andreas Tille <tillea@rki.de>
2000-09-01 02:36       ` Jan Wieck <janwieck@Yahoo.com>
2000-09-01 12:44         ` Andreas Tille <tillea@rki.de>
2000-08-23 16:46 ` Jie Liang <jliang@ipinc.com>
2000-08-24 09:36 ` hlefebvre <hlefebvre@lexbase.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox