Обсуждение: Error Returned by A Function

Hi all, I have a short function below that is return me an error, and I
can't figure out what I should do to fix it. Can anyone help? Archives have
not helped. The error I get is:

select * from current_neighbors(2);

ERROR:  cannot assign non-composite value to a row variable
CONTEXT:  PL/pgSQL function "current_neighbors" line 15 at assignment
------------------

CREATE TYPE typ_remote_net AS
   (remote_net varchar);

CREATE OR REPLACE FUNCTION current_neighbors(integer)
  RETURNS SETOF typ_remote_net AS
$BODY$

DECLARE

  work_if_id        ALIAS FOR $1;

  first_record          char(1);
  last_remote        varchar;
  last_neighbor_state    integer;
  output_count        integer := 0;
  returnValue        typ_remote_net;
  workarea        adns_neighbor_history%ROWTYPE;

BEGIN

  returnValue := 'none';
  first_record := 'Y';
  FOR workarea IN
    select if_id, updated_time, remote_net, neighbor_state,
      last_checked
    from adns_neighbor_history
    where if_id = work_if_id
    order by BY 1,3,2
  loop
    if first_record = 'N' then
      if workarea.remote_net = last_remote then
        if workarea.neighbor_state = last_neighborstate then
          -- same values, no action required
          NULL;
        else
          -- store latest neighbor state
          last_neighbor_state := workarea.neighbor_state;
        end if;
      else
        -- see if last remote needs to be reported
        if last_neighborstate > 0 then
          returnValue = last_remote;
          RETURN NEXT returnValue;
          output_count := output_count + 1;
        end if;
        last_remote := workarea.remote_net;
        last_neighbor_state := workarea.neighbor_state;
      end if;
    else
      first_record = 'N';
      last_remote := workarea.remote_net;
      last_neighbor_state := workarea.neighbor_state;
    end if;
  end loop;

  if (last_neighbor_state > 0) then
    RETURN NEXT returnValue;
  else
    if output_count = 0 then
      returnValue := 'none';
      RETURN NEXT returnValue;
    end if;
  end if;

RETURN;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Forgot to mention, I am using Windows 2003, PostgreSQL version 8.0.1.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Lane Van Ingen
Sent: Tuesday, January 10, 2006 10:43 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Error Returned by A Function

Hi all, I have a short function below that is returning me an error, and I
can't figure out what I should do to fix it. Can anyone help? Archives have
not helped. The error I get is:

select * from current_neighbors(2);

ERROR:  cannot assign non-composite value to a row variable
CONTEXT:  PL/pgSQL function "current_neighbors" line 15 at assignment
------------------

CREATE TYPE typ_remote_net AS
   (remote_net varchar);

CREATE OR REPLACE FUNCTION current_neighbors(integer)
  RETURNS SETOF typ_remote_net AS
$BODY$

DECLARE

  work_if_id        ALIAS FOR $1;

  first_record          char(1);
  last_remote        varchar;
  last_neighbor_state    integer;
  output_count        integer := 0;
  returnValue        typ_remote_net;
  workarea        adns_neighbor_history%ROWTYPE;

BEGIN

  returnValue := 'none';
  first_record := 'Y';
  FOR workarea IN
    select if_id, updated_time, remote_net, neighbor_state,
      last_checked
    from adns_neighbor_history
    where if_id = work_if_id
    order by BY 1,3,2
  loop
    if first_record = 'N' then
      if workarea.remote_net = last_remote then
        if workarea.neighbor_state = last_neighborstate then
          -- same values, no action required
          NULL;
        else
          -- store latest neighbor state
          last_neighbor_state := workarea.neighbor_state;
        end if;
      else
        -- see if last remote needs to be reported
        if last_neighborstate > 0 then
          returnValue = last_remote;
          RETURN NEXT returnValue;
          output_count := output_count + 1;
        end if;
        last_remote := workarea.remote_net;
        last_neighbor_state := workarea.neighbor_state;
      end if;
    else
      first_record = 'N';
      last_remote := workarea.remote_net;
      last_neighbor_state := workarea.neighbor_state;
    end if;
  end loop;

  if (last_neighbor_state > 0) then
    RETURN NEXT returnValue;
  else
    if output_count = 0 then
      returnValue := 'none';
      RETURN NEXT returnValue;
    end if;
  end if;

RETURN;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

"Lane Van Ingen" <lvaningen@esncc.com> writes:
> CREATE TYPE typ_remote_net AS
>    (remote_net varchar);
> ...
> DECLARE
>   returnValue        typ_remote_net;
> ...
>   returnValue := 'none';

Try assigning to returnValue.remote_net, instead.

            regards, tom lane

On Tue, Jan 10, 2006 at 10:42:39AM -0500, Lane Van Ingen wrote:
> select * from current_neighbors(2);
>
> ERROR:  cannot assign non-composite value to a row variable
> CONTEXT:  PL/pgSQL function "current_neighbors" line 15 at assignment

Line 15 of the function is:

>   returnValue := 'none';

You've declared returnValue to be a composite type (typ_remote_net)
so you need to assign to a particular column or use a row constructor:

    returnValue.remote_net := 'none';
    -- or
    returnValue := row('none');

Likewise in a few other places.  Also, the function's query has a
syntax error:

>     order by BY 1,3,2

--
Michael Fuhr