Re: need help with plpgsql execute insert - Mailing list pgsql-general

> developer@wexwarez.com 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 ,
>> 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 believe you are wrong.  the EXECUTE is being given 11 columns, it
> expects 1.  I think you need to form your execute query like;
>
> EXECUTE '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 (' || quote_ident(paymentId) || ',' || ...
>
> Something of that fashion.
>
>
>> I have tried all kinds of things but I truly have no idea what the
>> problem
>> is.
>> thanks
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>>                http://archives.postgresql.org/
>>
>>
>>
>
>
Thanks for replying.  So the '%' symbol can not be used with an EXECUTE
'INSERT statement.  I thought this was exactly what it was for?