Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: Using Postgresql Function via PHP part 1 & 2

    Hi...

    I'm having trouble with my postgresql function and i'm using php script to access the function. I want to insert data into a table that consists of 25 columns through the function. But i only have two columns/fields in my php script that will insert into the table and the rest columns/fields are inserted through the function itself (inserted from the value of another tables).

    Here is my pgsql function :

    -- Function: plankandua(text, text)

    -- DROP FUNCTION plankandua(text, text);

    CREATE OR REPLACE FUNCTION plankandua(_order_no text, _sub_itemno text)
    RETURNS boolean AS
    $BODY$
    declare
    rec_item record;
    rec_workequip record;
    rec_workstage record;
    rec_timereff record;
    rec_calendarresource record;
    jmlJamSisa integer;
    tglMulai text;
    nextDate text;
    tglAkhir text;
    jmlJamMulai integer;
    blmIns integer;
    rollset text;
    rolltime text;
    i integer;
    a integer;
    dueDate text;
    intOd integer;
    intThk integer;
    intLen integer;
    strKata text;
    jmlRow integer;
    rec_cal record;
    jmlSisaPlan integer;
    totalJam integer;
    sisaTotal integer;
    jmlJamTerpake integer;
    recloop record;
    jmlJamTersisa integer;

    begin
    for rec_item in select * from eng.checklist_subitem where order_no=_order_no and item_no=_item_no
    loop
    raise notice '%',rec_item.sub_itemno;
    rollset:='';
    --raise notice 'subitem : %',rec_item.sub_itemno;
    if rec_item.type_product='Can' or rec_item.type_product='Cone' then
    if (rec_item.od >=1000
    and rec_item.len <= 4100
    and rec_item.wtk >=38) then
    rollset:='ROLL4';
    end if;
    if (rec_item.od >=508 and rec_item.od <610
    and rec_item.len <= 3500
    and rec_item.wtk >=12 and rec_item.wtk <=25) then
    rollset:='ROLL5';
    end if;
    if (rec_item.od >=610 and rec_item.od <1000
    and rec_item.len <= 5100
    and rec_item.wtk >=26 and rec_item.wtk <=37) then
    rollset:='ROLL7';
    end if;

    --rollset:='P4ROLL';
    -- select semua workgroup selain roll
    raise notice 'ROLL : = % % n ', rollset,chr(10);
    if rollset='' or rollset =null then
    continue;
    end if;
    tglMulai:='';
    for rec_workstage in select * from workstage where ws_object='Plate' or ws_object='Can' or ws_object='General' order by ws_seqno::integer
    loop
    for rec_workequip in
    select equip_code,work_group from workequip where work_group in
    (rec_workstage.ws_code) limit 1
    loop
    raise notice '% % \n',rec_workequip.equip_code,chr(10);
    -- ini untuk roll khusus
    if rec_workequip.work_group='ROLL' then
    select * into rec_timereff from (select * from time_reff where resource_cd in (
    select equip_code from workequip where work_group in
    (select ws_code from workstage where ws_object='Plate' or ws_object='Can' or ws_object='General')
    )) k where od >= rec_item.od and thk>=rec_item.wtk and length >= rec_item.len
    and resource_cd =rollset order by thk,length,od limit 1;
    if found then
    select * into rec_calendarresource from (select *,case when id<>0 then
    (select count(*) as b from
    (select (each(planned)).key as key,(each(planned)).value as value from
    calendar_resource where id=g.id) o where o.value<>'0')end as
    bunga from calendar_resource g where primary_resource =rollset
    and dates in (select dates from calendar_resource where dates >=
    to_char(current_timestamp,'YYYY-MM-DD')::date group by dates)) c where
    bunga < 12
    order by dates,status limit 1;
    if tglMulai='' or tglMulai=null then
    tglMulai:=rec_calendarresource.dates;
    else
    tglMulai:=nextDate;
    end if;
    dueDate:= round(rec_timereff.proc_time::real::integer * rec_item.qty / 60,2)::integer;
    insert into work_planned(order_no,item_no,opr_no,operation,res ource_req,process_time_type,quantity,earliest_star t_date,due_date,complete)
    values (order_no,sub_itemno,rec_workstage.ws_seqno::integ er,rec_workstage.ws_desc,rollset,rec_timereff.proc _time,
    rec_item.qty,tglMulai,dueDate,false);
    --isi hstore 1
    for recloop in select * from (select *,case when id<>0 then (select count(*) from
    (select (each(planned)).key as key,(each(planned)).value as value from calendar_resource where id=g.id) o where o.value<>'0')end as
    bunga from calendar_resource g where primary_resource =rollset
    and dates in (select dates from calendar_resource where dates >=
    tglMulai::date group by dates)) c where
    bunga < 12
    order by dates,status limit 100
    loop
    select count(*) into jmlJamMulai from
    (select (each(planned)).key as key,(each(planned)).value as value from calendar_resource where id=recloop.id) k
    where k.value<>'0';

    select count(*) into jmlJamSisa from
    (select (each(planned)).key as key,(each(planned)).value as value from calendar_resource where id=recloop.id) l
    where l.value='0';

    to be continued.....part 2

  2. #2
    Join Date
    Mar 2010
    Posts
    9

    Using Postgresql Function via PHP (rev)

    Hi...

    I'm having trouble with my postgresql function and i'm using php script to access the function. I want to insert data into a table that consists of 25 columns through the function. But i only have two columns/fields in my php script that will insert into the table and the rest columns/fields are inserted through the function itself (inserted from the value of another tables).

    Here is my pgsql function : (see attachment plankandua.sql in ricky.zip)

    The two fields/columns that inserted into the work_planned table which i mentioned above is order_no and sub_itemno. And the rest fields/columns is inserted from another tables by calling and storing them in the function.

    And here is my php code : (see attachment inscust.php and insert.php in ricky.zip)

    When i press the submit button in inscust.php, it returned an error :
    " ERROR: syntax error at or near "VALUES" LINE 1: SELECT plankandua (order_no, item_no) VALUES ('1-2010-117','... ^ SELECT plankandua (order_no, item_no) VALUES ('1-2010-117','2.2') "

    Thanks for any help. If you any information or any data, please let me know.

    Ricky
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by rickyhutapea View Post
    And here is my php code
    This is a SQL forum not a PHP forum

    When i press the submit button in inscust.php, it returned an error :
    " ERROR: syntax error at or near "VALUES" LINE 1: SELECT plankandua (order_no, item_no) VALUES ('1-2010-117','... ^ SELECT plankandua (order_no, item_no) VALUES ('1-2010-117','2.2') "
    Post the full statement that throws the error, ideally together with the DDL for the table.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    LOL. When I saw the reply, I said, "No way, someone actually opened the zip and read through gobs of code..."

  5. #5
    Join Date
    May 2008
    Posts
    277
    The error is being generated by the database, not php (which is simply passing it through and displaying it to you). Since it's a syntax error, there's something wrong with your SQL query -- a missing comma or parenthesis or something.

    If the query is being dynamically generated, then your php may not be working correctly. Isolate the code that's generating the query and echo the query to the screen before sending it to the database so you can see what it's doing.


    Actually, on second glance, I think your error might be obvious:
    SELECT plankandua (order_no, item_no) VALUES ('1-2010-117','... ^
    SELECT plankandua (order_no, item_no) VALUES ('1-2010-117','2.2')
    This is not how you call a function. This is correct:
    Code:
    SELECT plankandua('1-2010-117', '2.2');
    This means you need to call the function for every row you want to insert.
    Last edited by futurity; 03-08-10 at 17:37.

  6. #6
    Join Date
    Mar 2010
    Posts
    9

    Thank You Futurity

    Thanks Futurity.....you solved my problem.
    Now it's working with no error.
    Once again thank you.

    Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •