Re: int8, primary key, seq scan - Mailing list pgsql-general

From Jeff Amiel
Subject Re: int8, primary key, seq scan
Date
Msg-id 4125ED4F.3030800@istreamimaging.com
Whole thread Raw
In response to int8, primary key, seq scan  (Jeff Amiel <jamiel@istreamimaging.com>)
List pgsql-general
sure....thanks for the assist....

CREATE TABLE public.draft
(
  amount numeric(10,2) NOT NULL,
  customer int4 NOT NULL,
  check_id bigserial NOT NULL,
  CONSTRAINT draft_pkey PRIMARY KEY (check_id)
) WITHOUT OIDS;

CREATE SEQUENCE public.draft_check_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 8223372036855129056
  CACHE 1;


CREATE OR REPLACE FUNCTION public.insert_draft(int4,  numeric)
  RETURNS draft AS
'DECLARE
  passed_customer ALIAS for $1;
  passed_amount ALIAS for $2;
  draftid int8;
  draftrow draft%rowtype;
BEGIN
insert into draft( amount, customer) values  (passed_amount,
passed_customer);
select into draftid currval(\'public.draft_check_id_seq\');
select into draftrow * from draft where check_id=int8(draftid);
return draftrow;
END;'
  LANGUAGE 'plpgsql' VOLATILE;




Tom Lane wrote:

>Jeff Amiel <jamiel@istreamimaging.com> writes:
>
>
>>I declared the compared value (draftid) as an int8, why should I have to
>>cast it as such in the query to cause the optimizer to use the primary key?
>>
>>
>
>Seems like it should work (and it does work for me, in a quick test with
>7.4.5).  Could we see the full text of the problematic function?
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
>
>


pgsql-general by date:

Previous
From: the inquirer
Date:
Subject: Authentication problems
Next
From: gausz@wp.pl (Michal Galusza)
Date:
Subject: Stored Procedures and libpq library