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

Search Discussions

  • Tom Lane at Mar 8, 2007 at 4:08 pm

    Sorin Schwimmer 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
  • Richard Huxton at Mar 8, 2007 at 4:11 pm

    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
  • Sorin Schwimmer at Mar 9, 2007 at 3:53 pm
    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
  • Tom Lane at Mar 10, 2007 at 5:42 am

    Sorin Schwimmer 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
  • Sorin Schwimmer at Mar 10, 2007 at 3:44 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMar 7, '07 at 11:30p
activeMar 10, '07 at 3:44p
posts6
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase