ERROR: there is no parameter $1 - Mailing list pgsql-general

From Steve Manes
Subject ERROR: there is no parameter $1
Date
Msg-id 473CA3CB.5050405@magpie.com
Whole thread Raw
Responses Re: ERROR: there is no parameter $1  (Steve Manes <smanes@magpie.com>)
List pgsql-general
This one has me stumped.  Does anyone know under which circumstances
this error would be returned by PHP's pg_query_params() even if the
procedure completed without an apparent error?

The procedure and PHP API code haven't changed in weeks.  I started
getting this after I upgraded the database to 8.3.

Here's the PHP call:

     ....

     $sql = 'SELECT insert_patient_person ($1, $2, $3, $4, $5, $6, NULL,
$7, $8, $9,
                                           $10, $11, $12, $13, $14, $15,
$16, $17, $18, $19,
                                           $20, $21, $22, $23, $24, $25,
$26, $27, $28, $29,
                                           ENTITY_INCOMPLETE())';

     $result = pg_query_params($trms->db, $sql, array(
                                 util_clean_str($post['first_name']),
                                 util_clean_str($post['middle_name']),
                                 util_clean_str($post['last_name']),
                                 $post['ss_num'],
                                 $post['ref_language_id'],
                                 $post['ref_gender_type_id'],
                                 $post['ref_race_type_id'],
                                 util_clean_str($post['address1']),
                                 util_clean_str($post['address2']),
                                 util_clean_str($post['city']),
                                 $post['ref_state_id'],
                                 util_clean_str($post['postal_code']),
                                 util_clean_str($post['email']),
                                 util_clean_str($post['phone1']),
                                 $post['ref_phone1_type_id'],
                                 util_clean_str($post['phone2']),
                                 $post['ref_phone2_type_id'],
                                 util_clean_str($post['phone3']),
                                 $post['ref_phone3_type_id'],
                                 util_clean_str($post['alias_name']),
                                 $post['dob'],
                                 $post['mr_num'],
                                 $post['ehris_num'],
                                 $post['medicaid_num'],
                                 $post['other_num'],
                                 $post['is_shelter_resident'],
                                 $post['is_icm'],
                                 util_clean_str($post['comments']),
                                 $post['caseworker_id']));

// debug('insert', $sql);

     $result = pg_query($trms->db, $sql);

     if ($result === false) {
         return array(null, "New patient insert failed.<br>" .
pg_last_error($trms->db));
     }

     $patient_id = pg_fetch_result($result, 0, 0);

     return array($patient_id, "Patient insert succeeded.");


--- And here's the stored procedure (with debugging strings)

------------------------------------------------------------
-- Insert a new patient with new person and demographic.
--
-- Returns: new patient insert_id
--          -1 (error)
------------------------------------------------------------

CREATE OR REPLACE FUNCTION insert_patient_person (
     v_first_name            VARCHAR,
     v_middle_name           VARCHAR,
     v_last_name             VARCHAR,
     v_ss_num                VARCHAR,
     v_ref_language_id       INTEGER,
     v_ref_gender_type_id    INTEGER,
     v_acl_group_id          INTEGER,
     v_ref_race_type_id      INTEGER,
     v_address1              VARCHAR,
     v_address2              VARCHAR,
     v_city                  VARCHAR,
     v_state_id              INTEGER,
     v_postal_code           VARCHAR,
     v_email                 VARCHAR,
     v_phone1                VARCHAR,
     v_ref_phone1_type_id    INTEGER,
     v_phone2                VARCHAR,
     v_ref_phone2_type_id    INTEGER,
     v_phone3                VARCHAR,
     v_ref_phone3_type_id    INTEGER,
     v_alias_name            VARCHAR,
     v_dob                   VARCHAR,
     v_mr_num                VARCHAR,
     v_ehris_num             VARCHAR,
     v_medicaid_num          VARCHAR,
     v_other_num             VARCHAR,
     v_is_shelter_resident   BOOLEAN,
     v_is_icm                BOOLEAN,
     v_comments              TEXT,
     v_caseworker_person_id  INTEGER,
     v_entity_status         INTEGER) RETURNS INTEGER AS $$
DECLARE
     d_person_insert_id      person.person_id%TYPE;
     d_patient_insert_id     patient.patient_id%TYPE;
     d_person_associate_id   person_associate.person_associate_id%TYPE;
BEGIN

     -- Insert a new person and demographic record.
RAISE INFO 'insert_patient_person: 1';

     SELECT INTO d_person_insert_id
         insert_person_and_demographic (
             CAST('' AS NAME),
             CAST('' AS VARCHAR),
             true,
             v_first_name,
             v_middle_name,
             v_last_name,
             v_ss_num,
             v_dob,
             false,
             false,
             v_ref_language_id,
             v_ref_gender_type_id,
             v_acl_group_id,
             v_ref_race_type_id,
             v_address1,
             v_address2,
             v_city,
             v_state_id,
             'USA',
             v_postal_code,
             v_email,
             v_phone1,
             v_ref_phone1_type_id,
             v_phone2,
             v_ref_phone2_type_id,
             v_phone3,
             v_ref_phone3_type_id,
             CAST('' AS TEXT));

     IF d_person_insert_id < 1 THEN
         RAISE NOTICE 'insert_patient_person: Could not insert new
person record';
         ROLLBACK;
         RETURN -1;
     END IF;

     -- Insert a new patient record.
RAISE INFO 'insert_patient_person: 2';

     SELECT INTO d_patient_insert_id
         insert_patient (
             NULL,
             d_person_insert_id,
             v_alias_name,
             v_mr_num,
             v_ehris_num,
             v_medicaid_num,
             v_other_num,
             v_is_shelter_resident,
             v_is_icm,
             v_comments,
             v_entity_status);

     IF d_patient_insert_id < 1 THEN
         RAISE NOTICE 'insert_patient_person: Could not insert new
patient record';
         ROLLBACK;
         RETURN -1;
     END IF;


     -- Insert a new caseworker record (if we have one)
RAISE INFO 'insert_patient_person: 3';

     IF v_caseworker_person_id > 0 THEN

         SELECT INTO d_person_associate_id
             insert_person_associate (
                 d_person_insert_id,
                 v_caseworker_person_id,
                 get_person_associate_type_id('caseworker'));

         IF d_person_associate_id < 0 THEN
             RAISE NOTICE 'insert_patient_person: Could not insert new
caseworker record';
             ROLLBACK;
             RETURN -1;
         END IF;
     END IF;
RAISE INFO 'insert_patient_person: done';

     RETURN d_patient_insert_id;

--    EXCEPTION
--        WHEN others THEN RETURN -1;
END;
$$ LANGUAGE plpgsql;


--- and here's the log output

Nov 15 14:31:24 jack postgres[48240]: [1-1] INFO:  insert_patient_person: 1
Nov 15 14:31:24 jack postgres[48240]: [2-1] INFO:  insert_patient_person: 2
Nov 15 14:31:24 jack postgres[48240]: [3-1] INFO:  insert_patient_person: 3
Nov 15 14:31:24 jack postgres[48240]: [4-1] INFO:
insert_patient_person: done
Nov 15 14:31:24 jack postgres[48240]: [5-1] ERROR:  there is no parameter $1
Nov 15 14:31:24 jack postgres[48240]: [5-2] STATEMENT:  SELECT
insert_patient_person ($1, $2, $3, $4, $5, $6, NULL, $7, $8, $9,
Nov 15 14:31:24 jack postgres[48240]: [5-3]
                   $10, $11, $12, $13, $14, $15, $16, $17, $18,
$19,
Nov 15 14:31:24 jack postgres[48240]: [5-4]
                   $20, $21, $22, $23, $24, $25, $26, $27, $28,
$29,
Nov 15 14:31:24 jack postgres[48240]: [5-5]
                   ENTITY_INCOMPLETE())

pgsql-general by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Variable LIMIT and OFFSET in SELECTs
Next
From: Steve Manes
Date:
Subject: Re: ERROR: there is no parameter $1