Thread: PostgreSQL function to create index from parent table.
Hi guys,
I want to copy indexes to child table from parent table, something like this:
CREATE OR REPLACE FUNCTION CREATE_INDEX_FOR_CHILD()
RETURNS void AS
$$
DECLARE
formal_table text;
BEGIN
FOR formal_table IN
SELECT 'CREATE '
|| CASE
WHEN i.indisunique THEN 'UNIQUE '
ELSE ''
END
|| 'INDEX '
|| 'P1_m7_s_'
|| c2.relname
|| ' ON '
||
'P1_m7_s'
|| ' USING btree ( '
|| split_part(split_part(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), '(', 2), ')', 1)
|| ' ); '
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i
ON(c.oid = i.indrelid)
JOIN pg_catalog.pg_class c2
ON(i.indexrelid = c2.oid)
JOIN pg_namespace nr
ON(nr.oid = c.relnamespace)
WHERE c.relname = 'm7_s'
loop
EXECUTE formal_table;
END LOOP;
END
$$ LANGUAGE plpgsql;
When I execute this function “SELECT CREATE_INDEX_FOR_CHILD();”, it fails with the below error.
INFO: Exception occurred : state : 42601 message : query has no destination for result data detail : hint : If you want to discard the results of a SELECT, use PERFORM instead. context : PL/pgSQL function inline_code_block line 90 at SQL statement
Query returned successfully with no result in 156 ms.
Can someone help me understand what is the problem with this code ? How to resolve it?
Thanks in advance,
Sekhar
(This doesn't really have anything to do with the ODBC driver. But see answer below.) On 07/25/2016 12:58 PM, Venkatesan, Sekhar wrote: > I want to copy indexes to child table from parent table, something like this: > > CREATE OR REPLACE FUNCTION CREATE_INDEX_FOR_CHILD() > RETURNS void AS > $$ > DECLARE > formal_table text; > BEGIN > FOR formal_table IN > SELECT 'CREATE ' > || CASE > WHEN i.indisunique THEN 'UNIQUE ' > ELSE '' > END > || 'INDEX ' > || 'P1_m7_s_' > || c2.relname > || ' ON ' > || > 'P1_m7_s' > || ' USING btree ( ' > || split_part(split_part(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), '(', 2), ')', 1) > || ' ); ' > FROM pg_catalog.pg_class c > JOIN pg_catalog.pg_index i > ON(c.oid = i.indrelid) > JOIN pg_catalog.pg_class c2 > ON(i.indexrelid = c2.oid) > JOIN pg_namespace nr > ON(nr.oid = c.relnamespace) > WHERE c.relname = 'm7_s' > loop > EXECUTE formal_table; > END LOOP; > END > $$ LANGUAGE plpgsql; > > When I execute this function "SELECT CREATE_INDEX_FOR_CHILD();", it fails with the below error. > > INFO: Exception occurred : state : 42601 message : query has no destination for result data detail : hint: If you want to discard the results of a SELECT, use PERFORM instead. context : PL/pgSQL function inline_code_blockline 90 at SQL statement > Query returned successfully with no result in 156 ms. > > Can someone help me understand what is the problem with this code ? How to resolve it? PL/pgSQL requires using PERFORM instead of SELECT, when the query doesn't return a result set, or you don't plain "SELECT ..." queries. For example, this doesn't work: do $$ begin SELECT pg_sleep(1); end; $$; ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement But this works: begin PERFORM call_functio(); end; Now, I'm not sure where in your query or application you have that situation. The function you pasted above seems OK in that regard. I think there's something more involved, because the error said "context : PL/pgSQL function inline_code_block line 90 at SQL statement", but there aren't 90 lines in that snippet. - Heikki
Never mind. I have resolved this problem. Thanks, Sekhar -----Original Message----- From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki Linnakangas Sent: Monday, July 25, 2016 4:31 PM To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org Subject: Re: [ODBC] PostgreSQL function to create index from parent table. (This doesn't really have anything to do with the ODBC driver. But see answer below.) On 07/25/2016 12:58 PM, Venkatesan, Sekhar wrote: > I want to copy indexes to child table from parent table, something like this: > > CREATE OR REPLACE FUNCTION CREATE_INDEX_FOR_CHILD() RETURNS void AS $$ > DECLARE formal_table text; BEGIN FOR formal_table IN > SELECT 'CREATE ' > || CASE > WHEN i.indisunique THEN 'UNIQUE ' > ELSE '' > END > || 'INDEX ' > || 'P1_m7_s_' > || c2.relname > || ' ON ' > || > 'P1_m7_s' > || ' USING btree ( ' > || split_part(split_part(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), '(', 2), ')', 1) > || ' ); ' > FROM pg_catalog.pg_class c > JOIN pg_catalog.pg_index i > ON(c.oid = i.indrelid) > JOIN pg_catalog.pg_class c2 > ON(i.indexrelid = c2.oid) > JOIN pg_namespace nr > ON(nr.oid = c.relnamespace) > WHERE c.relname = 'm7_s' > loop > EXECUTE formal_table; > END LOOP; > END > $$ LANGUAGE plpgsql; > > When I execute this function "SELECT CREATE_INDEX_FOR_CHILD();", it fails with the below error. > > INFO: Exception occurred : state : 42601 message : query has no destination for result data detail : hint: If you want to discard the results of a SELECT, use PERFORM instead. context : PL/pgSQL function inline_code_blockline 90 at SQL statement > Query returned successfully with no result in 156 ms. > > Can someone help me understand what is the problem with this code ? How to resolve it? PL/pgSQL requires using PERFORM instead of SELECT, when the query doesn't return a result set, or you don't plain "SELECT..." queries. For example, this doesn't work: do $$ begin SELECT pg_sleep(1); end; $$; ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement But this works: begin PERFORM call_functio(); end; Now, I'm not sure where in your query or application you have that situation. The function you pasted above seems OK in thatregard. I think there's something more involved, because the error said "context : PL/pgSQL function inline_code_block line 90 at SQL statement", but there aren't 90 lines in that snippet. - Heikki