CODEWITHSUNDEEP
postgresql
Jagdish
Jagdish

Reputation: 1

Unable to pass schema name in pg_get_serial_sequence

Unable to pass schema name in pg_get_serial_sequence. alerts is my schema name.

    do $$ 
    begin
    execute format('select setval(pg_get_serial_sequence(''' || alerts.nds_email_message ||                 ''', ''' || nds_email_message_id || '''),
        coalesce(max(nds_email_message_id), 0) + 1, false)
        from ''' || alerts.nds_email_message || '''');
    end $$;


    SQL Error [42601]: Unterminated dollar quote started at position 244 in SQL begin
    execute format('select setval(pg_get_serial_sequence(''' || alerts.nds_email_message ||         ''', ''' || nds_email_message_id || '''),
        coalesce(max(nds_email_message_id), 0) + 1, false)
        from ''' || alerts.nds_email_message || '''');
    end $$. Expected terminating $$'

  1. how can I add multiple setval statements inside the block.

     DO $$
 BEGIN

 SELECT             setval(pg_get_serial_sequence('alerts.incident_log','incident_log_id'),coale        sce(max(    incident_log_id),0) +1, false) from alerts.incident_log;
 SELECT             setval(pg_get_serial_sequence('alerts.nds_email_message','nds_email_message_        id'),coa    lesce(max(nds_email_message_id),0) +1, false) from         alerts.nds_email_message;
 SELECT             setval(pg_get_serial_sequence('alerts.nds_fax_message','nds_fax_message_id')        ,coalesc    e(max(nds_fax_message_id),0) +1, false) from         alerts.nds_fax_message;

 Error occurred during SQL query execution

 Reason:
 SQL Error [42601]: ERROR: query has no destination for result data
   Hint: If you want to discard the results of a SELECT, use PERFORM         instead.
   Where: PL/pgSQL function inline_code_block line 4 at SQL statement

Upvotes: 0

Views: 280

Answers (1)

user330315
user330315

Reputation:

As you are already using format() use the %L and %I placeholders:

do $$ 
begin
execute format('select setval(pg_get_serial_sequence(%L, %L), coalesce(max(%I), 0) + 1, false) from %I.%I', 
              'alerts.nds_email_message', 'nds_email_message_id', 'nds_email_message_id', 'alerts', 'nds_email_message');
end $$;

The parameters to pg_get_serial_sequence() are strings, so you need to use the %L placeholder. The references to the table and columns inside the actual select are identifiers, so you need to use %I

Upvotes: 1

Related Questions