Thread: stored procedure multiple call call question

Hi,


If I had a single table targ to insert into I would do an

  INSERT INTO targ SELECT thiscol, thatcol, theothercol FROM FOO.

The problem is that I have tables targ1, targ2, targn to insert things into and a nice stored procedure myproc which
doesthe insertion into all 3 tables - problem is that I dont see how I can effectively do  

   INSERT INTO myproc SELECT thiscol, thatcol, theothercol FROM FOO.

The only way I can work out how to do this is with another stored procedure which allows me to do:

   FOR rec IN SELECT thiscol, thatcol, theothercol
   FROM FOO
   LOOP
      PERFORM myproc(rec.thiscol, rec.thatcol, rec.theothercol);
   END LOOP;

But is there a way to do this just in SQL only without resorting to plpgsql or a language like C/Java?

thanks

c

my apologies - forgot to say I am on  postgresql 8.4.9 on Fedora Linux x86_64

c

> If I had a single table targ to insert into I would do an
>
>   INSERT INTO targ SELECT thiscol, thatcol, theothercol FROM FOO.
>
> The problem is that I have tables targ1, targ2, targn to insert things
into and a
> nice stored procedure myproc which does the insertion into all 3 tables -
> problem is that I dont see how I can effectively do
>
>    INSERT INTO myproc SELECT thiscol, thatcol, theothercol FROM FOO.
>
> The only way I can work out how to do this is with another stored
procedure
> which allows me to do:
>
>    FOR rec IN SELECT thiscol, thatcol, theothercol
>    FROM FOO
>    LOOP
>       PERFORM myproc(rec.thiscol, rec.thatcol, rec.theothercol);
>    END LOOP;
>
> But is there a way to do this just in SQL only without resorting to
plpgsql or a
> language like C/Java?
>
> thanks
>

SELECT myproc(thiscol, thatcol, theothercol) FROM FOO

If you want to use (and explode) the return value of "myproc" you will have
to do:

WITH func_exec AS (
SELECT myproc(thiscol, thatcol, theothercol) FROM FOO
)
SELECT (func_exec.myproc).*
FROM func_exec;

If you do:

SELECT myproc(thiscol, thatcol, theothercol).* FROM FOO

The "myproc" function will be executed one time for every output *column*
defined; which is likely to cause the statement to fail (since you'd be
inserting the same exact data multiple times).

David J.

Thanks very much for that David - really appreciate your response - it works like a dream

c

On Tuesday, 2 October 2012 19:42:59 UTC+1, Chris McDonald  wrote:
> Hi,
>
>
>
>
>
> If I had a single table targ to insert into I would do an
>
>
>
>   INSERT INTO targ SELECT thiscol, thatcol, theothercol FROM FOO.
>
>
>
> The problem is that I have tables targ1, targ2, targn to insert things into and a nice stored procedure myproc which
doesthe insertion into all 3 tables - problem is that I dont see how I can effectively do  
>
>
>
>    INSERT INTO myproc SELECT thiscol, thatcol, theothercol FROM FOO.
>
>
>
> The only way I can work out how to do this is with another stored procedure which allows me to do:
>
>
>
>    FOR rec IN SELECT thiscol, thatcol, theothercol
>
>    FROM FOO
>
>    LOOP
>
>       PERFORM myproc(rec.thiscol, rec.thatcol, rec.theothercol);
>
>    END LOOP;
>
>
>
> But is there a way to do this just in SQL only without resorting to plpgsql or a language like C/Java?
>
>
>
> thanks
>
>
>
> c

On 2012-10-02, Chris McDonald <chrisjonmcdonald@gmail.com> wrote:
> Hi,
>
>
> If I had a single table targ to insert into I would do an
>
>   INSERT INTO targ SELECT thiscol, thatcol, theothercol FROM FOO.
>
> The problem is that I have tables targ1, targ2, targn to insert things into and a nice stored procedure myproc which
doesthe insertion into all 3 tables - problem is that I dont see how I can effectively do  
>
>    INSERT INTO myproc SELECT thiscol, thatcol, theothercol FROM FOO.

see the chapter on partitioning for info on how to write a rule or
trigger to do it with syntax like that.

However with what you already have you you can do this:

   SELECT  myproc(thiscol, thatcol, theothercol) FROM FOO;


--
⚂⚃ 100% natural