From: | "Venkatesan, Sekhar" <sekhar(dot)venkatesan(at)emc(dot)com> |
---|---|
To: | Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL function to create index from parent table. |
Date: | 2016-07-28 06:15:40 |
Message-ID: | F84DE43FDACD4C45AA84E2DA016FAE2F1D111804@MX205CL01.corp.emc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
Never mind. I have resolved this problem.
Thanks,
Sekhar
-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnaka(at)gmail(dot)com] On Behalf Of Heikki Linnakangas
Sent: Monday, July 25, 2016 4:31 PM
To: Venkatesan, Sekhar; pgsql-odbc(at)postgresql(dot)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_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?
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Saito | 2016-07-31 12:12:35 | psqlODBC 09.05.0400 Released |
Previous Message | Inoue, Hiroshi | 2016-07-27 00:23:25 | Re: psqlODBC 09.05.0200 Released |