Postgres Pro
Downloads
Home   >   mailing lists

Thread: Inserts using plpgsql - Further

Inserts using plpgsql - Further

From
Scott Holmes
Date:
Well, it does not seem to be a problem with multiple fields but with fields
that are integers.

    INSERT INTO journal (j_date,casenmbr,j_descr,j_rate,j_status) VALUES
(jdate,cnmbr,erec.ev_title,erec.ev_rate,jstatus);

Fails, but

    INSERT INTO journal (j_date,j_descr,j_rate,j_status) VALUES
(jdate,erec.ev_title,erec.ev_rate,jstatus);

Succeeds.  The difference is casenmbr, an integer.  j_rate is a decimal value,
j_date is a date and the others are character fields.

Re: Inserts using plpgsql - Further

From
"Richard Huxton"
Date:
From: "Scott Holmes" <sholmes@pacificnet.net>

> Well, it does not seem to be a problem with multiple fields but with
fields
> that are integers.
>
>     INSERT INTO journal (j_date,casenmbr,j_descr,j_rate,j_status) VALUES
> (jdate,cnmbr,erec.ev_title,erec.ev_rate,jstatus);
>
> Fails, but
>
>     INSERT INTO journal (j_date,j_descr,j_rate,j_status) VALUES
> (jdate,erec.ev_title,erec.ev_rate,jstatus);
>
> Succeeds.  The difference is casenmbr, an integer.  j_rate is a decimal
value,
> j_date is a date and the others are character fields.

Puzzling:

drop table foo;

create table foo (a int unique not null, b text);

drop function foo_ins();

create function foo_ins() returns int as '
declare
  va int;
  vb text;
begin
  va := 1;
  vb := ''xxxxx'';
  INSERT INTO foo (a,b) VALUES (va,vb);
  return 1;
end;
' language 'plpgsql';

select foo_ins();
 foo_ins
---------
       1
(1 row)

select * from foo;
 a |   b
---+-------
 1 | xxxxx
(1 row)


Works fine here - v7.1.1 but I'm sure I'd have noticed problems in previous
versions if there'd been a fault. I'd suspect some issue with the type of
the variable or something.

What precisely is the error and is it possible to provide the table and
function definition?

- Richard Huxton

RE: Inserts using plpgsql - Further

From
"Andrew Snow"
Date:
> Puzzling

I am pretty sure there are some bugs in v7.1.x PL/PGSQL, they are difficult
to produce and nail down though.  A few of my functions that were working
fine in v7.0.x broke completely.  I am doing more testing and will submit
some bug reports...


- Andrew

Re: Inserts using plpgsql - Further

From
Tom Lane
Date:
"Andrew Snow" <andrew@modulus.org> writes:
> I am pretty sure there are some bugs in v7.1.x PL/PGSQL, they are difficult
> to produce and nail down though.

Especially if you don't even specify exactly which version you're using
;-)

Are you aware of the empty-SELECT-result-dumps-core bug in 7.1.1?  That
might explain some of your difficulties ...

            regards, tom lane