Обсуждение: 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 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