Search Postgresql Archives

Re: need help with plpgsql execute insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wednesday 20 December 2006 7:36 pm, developer@xxxxxxxxxxxx wrote:
> I am trying to loop through some data and then run insert some of the
> resulting data into a new table.  I can create the function but when I run
> it i get the error:
>
> ERROR: query "SELECT  'INSERT INTO payment (
> id,amount,accepted_date,  company_id ,  date ,
> patient_responsible_party_id ,  patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id,  type,   status)
> values (%,%,%,%,%,%,%,%,%,%)',  $1 , 0.0,  $2  ,  $3 ,  $4  ,  $5  ,  $6
> ,  $7 , 'Other', 'ACCEPTED'" returned 11 columns
> SQL state: 42601
> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute statement
>
> I don't understand what the "returned 11 columns" means.  I am inserting
> 10 and i counted and it all matches.
>
>
> Here is my function i deleted some of the sql.  The EXECUTE 'INSERT INTO
> is where the errors starts
>
> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
> DECLARE
>     data RECORD;
>     paymentId int;
> BEGIN
>  RAISE NOTICE 'Start loop...';
>
> FOR data IN select slra.company_id, slra.create_date,
> slra.service_line_responsibility_id,
> slr.insurance_policy_responsible_party_id,
> slr.patient_responsible_party_id,
> slr.patient_contact_responsible_party_id,
> insurer_service_center.insurer_network_id
> from
> .
> .
> .
> .
> .
> .
> .
> .
> .
> LOOP
>         -- Now "data" has one record
> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>
> EXECUTE 'INSERT INTO payment (
> id,amount,accepted_date,  company_id ,  date ,
> patient_responsible_party_id   patient_contact_responsible_party_id ,
                                               ^^^^ no comma

> insurer_network_responsible_party_id,  type,   status)
> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0,  data.create_date ,
> data.company_id,  data.create_date , data.patient_responsible_party_id ,
> data.patient_contact_responsible_party_id ,  data.insurer_network_id,
> 'Other', 'ACCEPTED';
>
>
> END LOOP;
>
>  RAISE NOTICE 'Done loop .';
>     RETURN 1;
> END;
> $$ LANGUAGE plpgsql;
> select add_missing_slrps() ;
>
>
> I assumed using the '%' symbol will automatically use the real value.
> Like if it is a date it will handle it like a java prepared statement.  Am
> I wrong?
>
> I have tried all kinds of things but I truly have no idea what the problem
> is.
> thanks
>

See inline comment, but I think you are missing a comma in your columns list.

-- 
Adrian Klaver
aklaver@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux