[SQL] BROBLEM IN BETWEEN QUERY (plpgsql)
hai all, I have a plpgsql function and I am using postgresl 8.1.9 CREATE OR REPLACE FUNCTION get_vehicle_id(INT) RETURNS SETOF RECORD AS $BODY$ DECLARE r RECORD; int_day ALIAS FOR $1; BEGIN FOR r in SELECT fk_bint_old_vehicle_number AS vehicle_id FROM tbl_rac_vehicle_replacement WHERE dat_replacement BETWEEN now() - interval '% day',int_day AND now() LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'; But i cant craete the function The error message is: ERROR: syntax error at or near "," at character 137 QUERY: SELECT fk_bint_old_vehicle_number AS vehicle_id FROM tbl_rac_vehicle_replacement WHERE dat_replacement BETWEEN now() - interval '% day', $1 AND now() CONTEXT: SQL statement in PL/PgSQL function "test" near line 11 LINE 1: ...E dat_replacement BETWEEN now() - interval '% day', $1 AND ... The problem line is BETWEEN now() - interval '% day', $1 AND now() I want to select dat_replacement between now () and now - 5 dyas or now -7 days like that i want to pass the integer value as argument to the function. I also tried like this WHERE dat_replacement BETWEEN now() - interval int_day day AND now(),but it also failed. Please help me . regards: Anoop
[SQL] how to returns set of records in PL/python
Hi , Iam new to plpython,how can I return a recordset from a plpython function? Is there is any way give me an example; plpgsql function CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF RECORD AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT pk_bint_country_id,vchr_country FROM tbl_country LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'; How i can get the same result using plpythonu and how I can call the function (is there any user defined type required like in plpgsql)? Thanks in advance Anoop
[SQL] How I can check a substring is a valid number in postgresql ?
Dear All, How I can check a substring is a valid number in postgresql ? example: I have a query select max(substring(code,2,length(code))::INT) from emp where substring(code,0,2) = 'A'; code A0001 A0002 ABC005 S0002 This query fails because of ABC002, how I can avoid this error , if there is a any way to check substring(code,1,length(code)) is a number ,then i can correct the query by put it in where condition. I want to get the result as 2(ie want to avoid checking ABC005 is there is any built in function to check the substring is a number in postgresql? Iam using postgres 8.1.8 pls help me thanks in advance: Anoop
[SQL] Problem in dynamic query execution in plpgsql
Hai all, I Have a problem,I have a plpgsql function == CREATE OR REPLACE FUNCTION function_to_get_ticket_wise_sales(VARCHAR,VARCHAR) RETURNS SETOF RECORD AS ' DECLARE dat_from_date ALIAS FOR $1; dat_to_date ALIAS FOR $2; dat_from DATE; dat_to DATE; vchr_query VARCHAR(1000); r RECORD; BEGIN dat_from := to_date(dat_from_date, ''DD MM ''); dat_to := to_date(dat_to_date,''DD MM ''); vchr_query := ''SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, dbl_market_fare_inv, dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, dbl_market_fare_inv AS flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN ''|| dat_from || '' AND '' || dat_to || '' ; RAISE NOTICE ''Query : % '',vchr_query; vchr_query := vchr_query || '' AND (vchr_our_lpo = '' '' OR vchr_our_lpo = "VS") ''; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; RETURN; END ' language 'plpgsql'; == my problems are: 1 problem : in RAISE NOTICE query string is print like this, SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, dbl_market_fare_inv, dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, dbl_market_fare_inv AS flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN 2008-04-01 AND 2008-07-10 when $1 = '2008-04-01' and $2 = '2008-04-10' , but i dont get the required result. I think that i will get the result if my query string will be like this(ie dates in single quote), : SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, dbl_market_fare_inv, dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, dbl_market_fare_inv AS flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN '2008-04-01' AND '2008-07-10 ' How i can put the dates in single quote in a dynamic query string? 2 problem: next problem is i have a varchar variable vchr_our_lpo how I can check is it containn an empty string or characters in a dynamic query string I tried different methods like, vchr_query :='' (vchr_our_lpo = '' '' OR vchr_our_lpo = "VS") ''; str_temp2:= ''VS''; vchr_query := '' (vchr_our_lpo = '' '' OR vchr_our_lpo = %) '',str_temp2; but all failed How I can solve these problem in a dynamic query string?.pls help me with a suitable example thanks in advance: Anoop G
Re: [SQL] Problem in dynamic query execution in plpgsql
Hai Kretschmer , Thanks for your reply, it help to solve my problem. I have few for doubts regarding dynamic query I have a table structure: Column | Type | Modifiers +--+--- mf | double precision | sf | double precision | comm | integer | I create a the following funtion create or replace function test_perc() returns setof record as $body$ declare vchr_query VARCHAR(100); r record; begin --vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) as flt_claim from calc'; vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim'; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; RETURN; end$body$ language 'plpgsql' function created but when I am traing to run this function I got the following error ERROR: syntax error at or near "–" at character 18 QUERY: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute statement LINE 1: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as... How i can solve this ? I tried another method to solve this I create a function to calculate the value create or replace function calc_perse(double precision,double precision,double precision) returns double precision as $body$ declare res double precision ; begin res :=( $1 - $1* $3/100 )- ($2 - $2*$3/100); return res; end$body$ language 'plpgsql' Then i try to call it from another function vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) as flt_claim from calc'; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; It also faild, How I can call a function like this from other function? thanks in advance Anoop G
[SQL] how to perform minus (-) operation in a dynamic query
Hai all, I am new to plpgsql ,I have a table structure: Column | Type | Modifiers +--+--- mf | double precision | sf | double precision | comm | integer | I create a the following funtion create or replace function test_perc() returns setof record as $body$ declare vchr_query VARCHAR(100); r record; begin vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim'; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; RETURN; end$body$ language 'plpgsql' function created but when I am traing to run this function I got the following error ERROR: syntax error at or near "–" at character 18 QUERY: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute statement LINE 1: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as... How I can solve this ,pls help me thanks in advance: Anoop
Re: [SQL] how to perform minus (-) operation in a dynamic query
Hai all, Thanks to all , I got the answer, actualy I am write the function in openoffice.org and paste it to a .sql file that is the reason for the error. thanks Anoop On Mon, Jul 14, 2008 at 7:32 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Anoop G" <[EMAIL PROTECTED]> writes: > > ERROR: syntax error at or near "\226" at character 18 > > QUERY: SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 sf * > comm/100) as > > flt_claim > > CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute > statement > > LINE 1: SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 sf * > comm/100) as... > > I'm not sure what character \226 is, but it's not a minus sign ... > >regards, tom lane >
[SQL] postgres time zone settings
Hai all, when i was going through the Postgresql documents, i came across a section which tells about the TIME ZONE settings what i understood from the document is, based on the value given in this conf file postgres will add or subtract the displacement hours to the system time assuming that the system time is the GMT. so there should be a configuration settings which will tell the postgres that the displacement is zero. How I can set the displacement is zero? Thanks in advance Anoop
Re: [SQL] postgres time zone settings(time difference in Server and client)
Hai all, I have database running on server. I am using python to run my application.postgres client is running on the clients .All clients are connected to a single database running on server. Each time application starts ,the login time is taken from server using the query """select CAST ( timeofday() AS timestamp""" But the problem the time which I got is different from that of server machine . I Think the postgres client is adding some values to the server time. How can I solve this .Is there any configuration setting for client to solve this?.In which location client's configuration file is stored( I am using Linux(Debian )?. here the result I got: The correct time in server: 2008-07-25 14:16:54 But What i got from client (with database cursor of sever database): 2008-07-25 19:46:33.46 (difference of more than 5 Hr ) Where this difference come? any one know the answer pls help me. Thanks in advance Anoop
Re: [SQL] postgres time zone settings(time difference in Server and client)
Hai all, my postgres version is PostgreSQL 8.1.8 didn't support clock_timestamp() regards: Anoop
Re: [SQL] Test if a table is present
you can find it throuh a query like this select tablename from pg_tables where tablename = 'tbl_updated_status_master'; regards: Anoop
[SQL] How to change a view's owner in postgres
Dear All, How to change a view's owner in postgres? thanks in advance: Anoop
[SQL] How to change a view's owner in postgres(is it possible?)
Dear all, We can change the owner of a tbale like this alter table tbl_year_end owner to anoop; Is it possible to change the owner name of a view through sql?
