From: | "Venkatesan, Sekhar" <sekhar(dot)venkatesan(at)emc(dot)com> |
---|---|
To: | "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org> |
Subject: | PostgreSQL function to create index from parent table. |
Date: | 2016-07-25 09:58:26 |
Message-ID: | F84DE43FDACD4C45AA84E2DA016FAE2F1D10FDF7@MX205CL01.corp.emc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2016-07-25 11:00:41 | Re: PostgreSQL function to create index from parent table. |
Previous Message | Pavel Raiskup | 2016-07-25 07:34:01 | Re: psqlODBC 09.05.0200 Released |