Thread: query ... returned 4 columns

Hi All,

I'm trying to write a stored PLPG/SQL procedure:

CREATE OR REPLACE FUNCTION
arch_expected_stuff(CHAR(12)) RETURNS VOID
AS $$
  -- Archives expected_stuff
  -- takes packing slip
  DECLARE
    o expected_stuff%ROWTYPE;
  BEGIN
    o:= * FROM expected_stuff WHERE packslip=$1; --
LIMIT 1;
    INSERT INTO archive.expected_stuff VALUES (o);
    DELETE FROM expected_stuff WHERE packslip=$1;
  END;
$$ LANGUAGE PLPGSQL;


When I issue a

select arch_expected_stuff('246');

I receive the following error:ERROR:  query "SELECT  *
FROM expected_stuff WHERE packslip= $1  LIMIT 1"
returned 4 columns
CONTEXT:  PL/pgSQL function "arch_expected_stuff" line
6 at assignment

Yes, both expected_stuff and archive.expected_stuff
have 4 columns. What is the error?

Thanks for your help,
Sorin



____________________________________________________________________________________
The fish are biting.
Get more visitors on your site using Yahoo! Search Marketing.
http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php

Sorin Schwimmer <sxn02@yahoo.com> writes:
>   DECLARE
>     o expected_stuff%ROWTYPE;
>   BEGIN
>     o:= * FROM expected_stuff WHERE packslip=$1; --

Use
    SELECT * INTO o FROM expected_stuff WHERE ...

The assignment syntax is currently only supported for scalar values,
I believe.

            regards, tom lane

Sorin Schwimmer wrote:
> Hi All,
>
> I'm trying to write a stored PLPG/SQL procedure:

>     o:= * FROM expected_stuff WHERE packslip=$1; --
> LIMIT 1;

Is this valid syntax? I'm a little surprised, but I think I can see
what's happening.

Try something more like:
   SELECT * INTO o FROM expected_stuff...

See if that makes a difference
--
   Richard Huxton
   Archonet Ltd

Thank you, indeed

SELECT * INTO o ...

solves it.

One last question, if I may:
both expected_stuff and archive.expected_stuff are
defined as:

( source CHAR(2);
  warehouse CHAR(1);
  stuff SMALLINT;
  packslip CHAR(12)
);

and o is expected_stuff%ROWTYPE

Having the same structure, I put
INSERT INTO archive.expected_stuff VALUES(o);

but it doesn't work. Instead, I had to rewrite as
INSERT ... VALUES (o.source,o.warehouse...);

Is the short version not supposed to work, or am I
using the wrong syntax?

Thanks again,
Sorin



____________________________________________________________________________________
Get your own web address.
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

Sorin Schwimmer <sxn02@yahoo.com> writes:
> Having the same structure, I put
> INSERT INTO archive.expected_stuff VALUES(o);
> but it doesn't work. Instead, I had to rewrite as
> INSERT ... VALUES (o.source,o.warehouse...);

Of course.  The former command implies that you are inserting a
composite value into a single composite-type column of expected_stuff,
which you are not.

The right way to express this IMHO is
    INSERT INTO archive.expected_stuff VALUES(o.*);
which should expand into the longhand notation "o.source,o.warehouse..."
in the same way that "SELECT o.* FROM ..." would do.  This does actually
work in 8.2 (and maybe 8.1, I forget).  In older releases you gotta
write it out longhand :-(

            regards, tom lane

I am running 8.1.4 and the o.* notation works.

Thanks again,
Sorin




____________________________________________________________________________________
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
http://answers.yahoo.com/dir/?link=list&sid=396546091