Return Multiple Rows from Store Function
Hi , Returning multiple rows from store functions using "RETURNS TABLE" and RETURN QUERY. The results set may have more than 50k records. Does it give any performance issues related to memory? if yes how to avoid it CREATE OR REPLACE FUNCTION funcq(COL1 character varying) ) RETURNS TABLE ( a VARCHAR, b VARCHAR, c varchar) AS $$ BEGIN RETURN QUERY SELECT a,b,c from table1 where C= COL1; END; $$ LANGUAGE plpgsql; -- Thanks & Regards, Brahmeswara Rao J.
How to set array element to null value
I'm trying to reset array element to null. but 3rd line of below snippet is giving the compilation error. FOR indx_1 IN array_lower(X, 1)..array_upper(X, 1) LOOP IF X[indx_1].REFERENCE_VALUE = 'ABC' THEN X[indx_1].REFERENCE_VALUE:=''; END IF; END LOOP; -- Thanks & Regards, Brahmeswara Rao J.
Re: How to remove elements from array .
Hi All,
My request is simple,
Just browse the results from a table into an array and loop through array
results to find out to unnecessary records and delete them based on certain
business conditions and print the rest of the records.
Below are the array results from table.
{"(20310,https://google.com,AP,BR,,Y)","(20310,https://google.com
,AP,,,N)","(20311,https://google.com,AP,,,N)"}
Tried to apply the Unnest on array results but giving an error at "https://
" .
Can we iterate over unnest records?
On Fri, Jul 6, 2018 at 1:56 PM, Pavel Stehule
wrote:
> Hi
>
> 2018-07-06 10:19 GMT+02:00 Charles Clavadetscher <
> clavadetsc...@swisspug.org>:
>
>> Hi
>>
>>
>>
>> *From:* Brahmam Eswar [mailto:brahmam1...@gmail.com]
>> *Sent:* Freitag, 6. Juli 2018 09:50
>> *To:* pgsql-general ;
>> pgsql-hack...@postgresql.org
>> *Subject:* How to remove elements from array .
>>
>>
>>
>> Hi ,
>>
>>
>>
>> I tried to use array_remove to remove elements from an array but it's
>> saying function doesn't exist . I'm able to use other array functions.
>>
>>
>>
>> 1) Capture the results with multiple columns into array .
>>
>> 2) if ay results exist then loop through an array to find out the record
>> with col1='Y'
>>
>> 3) If col1='Y' then get the respective value of Col2 (10) and delete the
>> similar records of col2 if exist.
>>
>>
>>
>> Col1Col2
>>
>> Y 10
>>
>> N 20
>>
>> N 10
>>
>>
>>
>> Need to delete record1 and record3.To delete the array records i'm using
>> array_remove but it says doesn't exist.
>>
>>
>>
>> Version pgadmin4 .
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Snippet :-
>>
>>
>>
>> CREATE or REPLACE FUNCTION FUNC1
>>
>> (
>>
>> << List of elements >>
>>
>> ) AS $$
>>
>>
>>
>> DECLARE
>>
>>
>>
>> TEST_CODES record1 ARRAY;
>>
>> TEMP_REF_VALUE VARCHAR(4000);
>>
>>
>>
>> BEGIN
>>
>> IS_VALID := 'S';
>>
>>
>>
>> SELECT ARRAY
>>
>> (SELECT ROW(Col1,Col2,COl3,Col4) ::record1
>>
>> FROM table1 INTO TEST_CODES
>>
>>
>>
>> IF array_length(TEST_CODES, 1) > 0 THEN
>>
>>
>>
>> FOR indx IN array_lower(TEST_CODES,
>> 1)..array_upper(TEST_CODES, 1) LOOP
>>
>>IF TEST_CODES[indx].COL1 = 'Y' THEN
>>
>> TEMP_REF_VALUE:=TEST_CODES[indx].Col2;
>>
>> TEST_CODES :=
>> array_remove(TEST_CODES,TEMP_REF_VALUE);
>>
>> END IF;
>>
>> END Loop;
>>
>> END IF;
>>
>>
>>
>>
>> --
>>
>> Thanks & Regards,
>> Brahmeswara Rao J.
>>
>>
>>
>> I am not so in clear why you are using arrays in a function for that.
>>
>> A solution with SQL would be:
>>
>
> I don't understand to the request too.
>
>
>>
>>
>> CREATE TABLE tst (
>>
>> col1 text,
>>
>> col2 integer
>>
>> );
>>
>>
>>
>
> Attention - temp table are expensive in Postgres (mainly for higher load),
> so what can be done simply with arrays should be done with arrays.
>
> Regards
>
> Pavel
>
>
>> INSERT INTO tst VALUES ('Y', 10), ('N', 20), ('N', 10);
>>
>>
>>
>> SELECT * FROM tst;
>>
>>
>>
>> col1 | col2
>>
>> --+--
>>
>> Y| 10
>>
>> N| 20
>>
>> N| 10
>>
>> (3 rows)
>>
>>
>>
>> DELETE FROM tst t
>>
>> USING (SELECT * FROM tst
>>
>>WHERE col1 = 'Y') AS x
>>
>> WHERE t.col2 = x.col2;
>>
>>
>>
>> SELECT * FROM tst;
>>
>>
>>
>> col1 | col2
>>
>> --+--
>>
>> N| 20
>>
>> (1 row)
>>
>>
>>
>> Regards
>>
>> Charles
>>
>
>
--
Thanks & Regards,
Brahmeswara Rao J.
How to remove elements from array .
Hi , I tried to use array_remove to remove elements from an array but it's saying function doesn't exist . I'm able to use other array functions. 1) Capture the results with multiple columns into array . 2) if ay results exist then loop through an array to find out the record with col1='Y' 3) If col1='Y' then get the respective value of Col2 (10) and delete the similar records of col2 if exist. Col1Col2 Y 10 N 20 N 10 Need to delete record1 and record3.To delete the array records i'm using array_remove but it says doesn't exist. Version pgadmin4 . Snippet :- CREATE or REPLACE FUNCTION FUNC1 ( << List of elements >> ) AS $$ DECLARE TEST_CODES record1 ARRAY; TEMP_REF_VALUE VARCHAR(4000); BEGIN IS_VALID := 'S'; SELECT ARRAY (SELECT ROW(Col1,Col2,COl3,Col4) ::record1 FROM table1 INTO TEST_CODES IF array_length(TEST_CODES, 1) > 0 THEN FOR indx IN array_lower(TEST_CODES, 1)..array_upper(TEST_CODES, 1) LOOP IF TEST_CODES[indx].COL1 = 'Y' THEN TEMP_REF_VALUE:=TEST_CODES[indx].Col2; TEST_CODES := array_remove(TEST_CODES,TEMP_REF_VALUE); END IF; END Loop; END IF; -- Thanks & Regards, Brahmeswara Rao J.
Re: [GENERAL] Schema Information .
Thanks David for your quick response. I'm using below query to pull the schema information ,but the count of rows in table is giving wrong ,i can see the count of records using select count(*) from . How do we get an exact number of rows in table. SELECT C.relname AS Table_Name, C.relnatts AS NUM_COLS, C.reltuples::bigint AS NUM_ROWS, C.relhastriggers AS Has_Triggers, C.relhasindex AS HAS_INDEX FROM pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE n.nspname='ap' and C.relkind='r' ORDER BY C.relname ; On Mon, Dec 4, 2017 at 9:20 PM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Mon, Dec 4, 2017 at 9:17 AM, Brahmam Eswar <brahmam1...@gmail.com> > wrote: > >> Hi , >> >> Is there anyway to pull the complete information of tables in a >> particular schema . >> >> Table_Name ,Num_Rows,Columns,Indexed_Columns,Triggers. >> >> >> >> The context of this request is ,we are migrating the database from Oracle >> to PostgreSQl,,so we need to verify the data after perform data migration >> from oracle. >> >> >> -- >> Thanks & Regards, >> Brahmeswara Rao J. >> > > >Is there anyway to pull the complete information of tables in a > particular schema . > > The following query will give you all the tables and columns in a schema: > > SELECT n.nspname AS schema, > c.relname AS table, > a.attname AS column, > a.attnum AS col_pos > FROM pg_namespace n > JOIN pg_class c ON c.relnamespace = n.oid > JOIN pg_attribute a ON a.attrelid = c.oid > WHERE n.nspname = 'your_schema' >AND relkind = 'r' >AND a.attnum > 0 > ORDER BY 1, 2, 4; > > To obtain information about indexed columns and triggers, you will also > need to query > pg_index and pg_trigger > > https://www.postgresql.org/docs/9.6/static/catalogs.html > > You might also find it easier to look at the information_schema > > <https://www.postgresql.org/docs/9.6/static/information-schema.html> > https://www.postgresql.org/docs/9.6/static/information-schema.html > > > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. > -- Thanks & Regards, Brahmeswara Rao J.
[GENERAL] Multiple key error .
Unable to add multiple keys to the table. ALTER TABLE table1 ADD CONSTRAINT Table1_PK PRIMARY KEY (Col1,Col2); ERROR: multiple primary keys for table "table1" are not allowed SQL state: 42P16 -- Thanks & Regards, Brahmeswara Rao J.
[GENERAL] Date Time data types
What is difference between CLOCK_TIMESTAMP() and CURRENT_TIMESTAMP? Is there any specific use case to differentiate them . -- Thanks & Regards, Brahmeswara Rao J.
[GENERAL] Reset Sequence number
we are in process of migrating to postgres and need to reset the sequence number with highest value of table key . I want to make it procedural to do that as mentioned below,But it's throwing an error . DO $$ DECLARE SEQ BIGINT; BEGIN SEQ:=(SELECT MAX(ID) FROM TABLE_1); ALTER SEQUENCE TABLE_1_SEQ RESTART WITH SEQ; END$$; Error : syntax error at or near "SEQ" -- Thanks & Regards, Brahmeswara Rao J.
