PG_CRON logging
Hi, Is there way to insert the cron job execution status to a table? Or any other method to identify the job status without checking the log file? *Regards,* *Rajin *
Regarding TZ conversion
Hi , What is the right approach for using AT TIME ZONE function? Option 1: AT TIME ZONE 'IST' Option 2: AT TIME ZONE 'Asia/Kolkata' In the first option, I get +2:00:00 offset (when *timezone_abbrevations = 'Default'*) and for option 2 , +5:30 offset. I can see multiple entries for IST in pg_timezone_names with different utc_offset, but in pg_timezone_abbrev there is one entry. I guess AT TIME ZONE function using the offset shown in pg_timezone_abbrev. ovdb=> select * from pg_timezone_names where abbrev = 'IST'; name | abbrev | utc_offset | is_dst -+++ Asia/Calcutta | IST| 05:30:00 | f Asia/Kolkata| IST| 05:30:00 | f Europe/Dublin | IST| 01:00:00 | t posix/Asia/Calcutta | IST| 05:30:00 | f posix/Asia/Kolkata | IST| 05:30:00 | f posix/Europe/Dublin | IST| 01:00:00 | t posix/Eire | IST| 01:00:00 | t Eire| IST| 01:00:00 | t ovdb=> select * from pg_timezone_abbrevs where abbrev = 'IST'; abbrev | utc_offset | is_dst ++ IST| 02:00:00 | f In my system, we receive TZ in abbrev format (3 character, like EST, PST ...). I have tried changing the timezone_abbrevations = 'India', then it worked fine (IST is giving +5:30 offset) So, What is recommended, use name instead of abbrev in TZ conversion function? Or Change the timezone_abbrevations to 'India'? *Regards,* *Rajin *
Re: Regarding TZ conversion
Thanks for the clarification. Is it advisable to modify the Default? Will it override when we apply a patch or upgrade the DB? What about creating a new file like below and update the postgres.conf with the new name. # New tz offset @INCLUDE Default @OVERRDIE IST 19800 *Regards,* *Rajin * On Thu, Jun 4, 2020 at 7:23 PM Tom Lane wrote: > Rajin Raj writes: > > Option 1: AT TIME ZONE 'IST' > > Option 2: AT TIME ZONE 'Asia/Kolkata' > > In the first option, I get +2:00:00 offset (when *timezone_abbrevations = > > 'Default'*) and for option 2 , +5:30 offset. > > > I can see multiple entries for IST in pg_timezone_names with > > different utc_offset, but in pg_timezone_abbrev there is one entry. I > guess > > AT TIME ZONE function using the offset shown in pg_timezone_abbrev. > > No. If you use an abbreviation rather than a spelled-out zone name, > you get whatever the timezone_abbrevations file says, which by default > is > > $ grep IST .../postgresql/share/timezonesets/Default > # CONFLICT! IST is not unique > # - IST: Irish Standard Time (Europe) > # - IST: Indian Standard Time (Asia) > IST 7200# Israel Standard Time > > If that's not what you want, change it. See > > https://www.postgresql.org/docs/current/datetime-config-files.html > > and also > > > https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES > > regards, tom lane >
PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
Is there any impact of using the character varying without providing the length while creating tables? I have created two tables and inserted 1M records. But I don't see any difference in pg_class. (size, relpage) create table test_1(name varchar); create table test_2(name varchar(50)); insert into test_1 ... 10M records insert into test_2 ... 10M records vacuum (full,analyze) db_size_test_1; vacuum (full,analyze) db_size_test_2; Which option is recommended? *Regards,* *Rajin *
Table as argument in postgres function
I'm trying to convert SAP Hana procedures in PG and i'm not able to handle below scenario in Postgres 11 Scenario: I want to pass a table (Multiple rows) to function and use it inside as a temp table. Sample Code: create a table tbl_id (id int, name character varying (10)); insert few rows to tbl_id; create a function myfun (in tt_table ) begin return setof table(few columns) begin as select id,name into lv_var1,lv_var2; from tt_table --> Want to use the input table where id = ; return query select * from tbl2 where id in (select id from tt_table); --> Want to use the input table end; I don't want to go with dynamic sql, is there any other way to declare a table as input argument and use it a normal temp table inside the function body? --> Function invocation issue: select * from myfun(tbl_id); How to invoke a function by passing a table as argument?
Table as argument in postgres function
Hi, I'm trying to convert SAP Hana procedures in PG and i'm not able to handle below scenario in Postgres 11 Scenario: I want to pass a table (Multiple rows) to function and use it inside as a temp table. Sample Code: create a table tbl_id (id int, name character varying (10)); insert few rows to tbl_id; create a function myfun (in tt_table ) begin return setof table(few columns) begin as select id,name into lv_var1,lv_var2; from tt_table --> Want to use the input table where id = ; return query select * from tbl2 where id in (select id from tt_table); --> Want to use the input table end; I don't want to go with dynamic sql, is there any other way to declare a table as input argument and use it a normal temp table inside the function body? --> Function invocation issue: select * from myfun(tbl_id); How to invoke a function by passing a table as argument? Regards, Rajin
Alternate methods for multiple rows input/output to a function.
--> Function ' filter_id ' filters the ID's based on some conditions. --> Input is set of ID's. (Not directly taking the input since there is no provision to pass multiple rows to a function) create function filter_id() return table (id bigint) begin --> Assuming input table is already created #temp_input_id retun query as select id from tbl a inner join #temp_input_id b on (a.id = b.id) where a.; end; --> Calling Function: create function caller() return table (id bigint,col1 bigint, col2 bigint) begin --> do some processing --> Find out the input id's for filtering. --> Create temp table for providing input for the filtering function create temp table #TEMP1 as select id from tbla; (Cannot move the input id logic to filter_function) --> calling the filter function create temp table #TEMP2 as select * from filter_id(); --> This is a generic function used in many functions. return query as select a.* from tb3 a inner join tb4 inner join tb 5 inner join #TEMP2; end; Is there any alternate way of achieving this? Passing multiple records to a function im creating a temp table before invoking the function. For receiving an output of multiple rows i'm creating a temp table to reuse further in the code. Can this be done using Refcursor? Is it possible to convert refcursor to a temp table and use it as normal table in query?
Table as argument in postgres function
I'm trying to convert SAP Hana procedures in PG and i'm not able to handle below scenario in Postgres 11 Scenario: I want to pass a table (Multiple rows) to function and use it inside as a temp table. Sample Code: create a table tbl_id (id int, name character varying (10)); insert few rows to tbl_id; create a function myfun (in tt_table ) begin return setof table(few columns) begin as select id,name into lv_var1,lv_var2; from tt_table --> Want to use the input table where id = ; return query select * from tbl2 where id in (select id from tt_table); --> Want to use the input table end; I don't want to go with dynamic sql, is there any other way to declare a table as input argument and use it a normal temp table inside the function body? --> Function invocation issue: select * from myfun(tbl_id); How to invoke a function by passing a table as argument?
Table as argument in postgres function
Hi, I'm trying to convert SAP Hana procedures in PG and i'm not able to handle below scenario in Postgres 11 Scenario: I want to pass a table (Multiple rows) to function and use it inside as a temp table. Sample Code: create a table tbl_id (id int, name character varying (10)); insert few rows to tbl_id; create a function myfun (in tt_table ) begin return setof table(few columns) begin as select id,name into lv_var1,lv_var2; from tt_table --> Want to use the input table where id = ; return query select * from tbl2 where id in (select id from tt_table); --> Want to use the input table end; I don't want to go with dynamic sql, is there any other way to declare a table as input argument and use it a normal temp table inside the function body? --> Function invocation issue: select * from myfun(tbl_id); How to invoke a function by passing a table as argument? Regards, Rajin
Alternate methods for multiple rows input/output to a function.
--> Function ' filter_id ' filters the ID's based on some conditions. --> Input is set of ID's. (Not directly taking the input since there is no provision to pass multiple rows to a function) create function filter_id() return table (id bigint) begin --> Assuming input table is already created #temp_input_id retun query as select id from tbl a inner join #temp_input_id b on (a.id = b.id) where a.; end; --> Calling Function: create function caller() return table (id bigint,col1 bigint, col2 bigint) begin --> do some processing --> Find out the input id's for filtering. --> Create temp table for providing input for the filtering function create temp table #TEMP1 as select id from tbla; (Cannot move the input id logic to filter_function) --> calling the filter function create temp table #TEMP2 as select * from filter_id(); --> This is a generic function used in many functions. return query as select a.* from tb3 a inner join tb4 inner join tb 5 inner join #TEMP2; end; Is there any alternate way of achieving this? Passing multiple records to a function im creating a temp table before invoking the function. For receiving an output of multiple rows i'm creating a temp table to reuse further in the code. Can this be done using Refcursor? Is it possible to convert refcursor to a temp table and use it as normal table in query?
