Ygthkb

Modern Algebraic Geometry and Analytic Number Theory

Other than edits for international editions, did Harry Potter and the Philosopher's Stone receive errata?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Maybe pigeonhole problem?

Why do objects rebound after hitting the ground?

When using Volatility with a memory image, what is the Kernel version?

What is a good way to explain how a character can produce flames from their body?

Coworker asking me to not bring cakes due to self control issue. What should I do?

As a junior, is it inappropriate for my supervisor to expect me to study our stack in my own time?

Democratic Socialism vs Social Democracy

Is there a way to pause a running process on Linux systems and resume later?

Critique vs nitpicking

Was Claire Dearing blamed for any of Jurassic World's failings?

What would be some possible ways of escaping higher gravity planets?

How can I automatically launch GPSD on startup?

Who is credited for the syntax tree in synthetic linguistics

"Starve to death" Vs. "Starve to the point of death"

Was there a pre-determined arrangement for the division of Germany in case it surrendered before any Soviet forces entered its territory?

How to put text above column in minipage?

The relationship between entanglement of vector states to matrix operations

If I tried and failed to start my own business, how do I apply for a job without job experience?

How to check if remote-signer is working as expected?

Is it possible to detect 100% of SQLi with a simple regex?

Why did Luke use his left hand to shoot?

Sort Output of plpgsql Script

Recursive query using plpgsqlSlow fulltext search due to wildly inaccurate row estimatesHow to Insert records from remote database to local database using dblink in postgresql 9.3SELECT in trigger function in two tablesPostgreSQL 9.5, getting “cached plan must not change result type” errorPostgres full text search with better matchPostgres: query on huge (11gb ) index does not returnPostgresql: Insert trigger function from select query, plus static values pulled from the new.<record>Loop through slices of 2-dimensional array in plpgsql function?PLPGSQL function(multiple column output)--gives error

0

I'm using Postgres 9.6

I'm creating a script that compares two schema which have the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples attribute found in the information schema because it is not guaranteed to be correct.

So far I have this function:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(schema1 text, schema2 text) 

RETURNS TABLE(tablename text, schema1_ct integer, schema2_ct integer, match boolean) AS $$

DECLARE

    schema1_tables CURSOR FOR

        select pg_tables.tablename

        from pg_tables

        where schemaname ~ schema1

        order by tablename;

        schema1_ct int;

        schema2_ct int;

BEGIN

    FOR table_record IN schema1_tables LOOP

        EXECUTE 'SELECT count(*) FROM ' || schema1 || '.' || table_record.tablename INTO schema1_ct

        EXECUTE 'SELECT count(*) FROM ' || schema2 || '.' || table_record.tablename INTO schema2_ct

        RETURN QUERY EXECUTE 'SELECT ''' || table_record.tablename || '''::text,' || schema1_ct || ',' || schema2_ct || ',' (schema1_ct >= 0) = (schema2_ct >= 0)

END LOOP

END; $$ LANGUAGE pgpgsql

But I'm really only interested in the cases where the output's "match" column is false, that is, one table is empty and one table is not empty. How I can I sort my output so that row where match = False are shown first?

postgresql postgresql-9.6 plpgsql

share|improve this question

asked Feb 12 at 18:07

DerekDerek

32

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How I can I sort my output so that row where match = False are shown first? order by match, ...
  
  – Akina
  Feb 12 at 19:30
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Typos like pgpgsql and missing ; indicate that's hand-knit dummy code. Please show what you actually tested. And have you considered running ANALYZE on involved tables? Then you can work with pg_class.reltuples ...
  
  – Erwin Brandstetter
  1 hour ago
  

  
  
  
  

add a comment | 

0

I'm using Postgres 9.6

I'm creating a script that compares two schema which have the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples attribute found in the information schema because it is not guaranteed to be correct.

So far I have this function:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(schema1 text, schema2 text) 

RETURNS TABLE(tablename text, schema1_ct integer, schema2_ct integer, match boolean) AS $$

DECLARE

    schema1_tables CURSOR FOR

        select pg_tables.tablename

        from pg_tables

        where schemaname ~ schema1

        order by tablename;

        schema1_ct int;

        schema2_ct int;

BEGIN

    FOR table_record IN schema1_tables LOOP

        EXECUTE 'SELECT count(*) FROM ' || schema1 || '.' || table_record.tablename INTO schema1_ct

        EXECUTE 'SELECT count(*) FROM ' || schema2 || '.' || table_record.tablename INTO schema2_ct

        RETURN QUERY EXECUTE 'SELECT ''' || table_record.tablename || '''::text,' || schema1_ct || ',' || schema2_ct || ',' (schema1_ct >= 0) = (schema2_ct >= 0)

END LOOP

END; $$ LANGUAGE pgpgsql

But I'm really only interested in the cases where the output's "match" column is false, that is, one table is empty and one table is not empty. How I can I sort my output so that row where match = False are shown first?

postgresql postgresql-9.6 plpgsql

share|improve this question

asked Feb 12 at 18:07

DerekDerek

32

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How I can I sort my output so that row where match = False are shown first? order by match, ...
  
  – Akina
  Feb 12 at 19:30
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Typos like pgpgsql and missing ; indicate that's hand-knit dummy code. Please show what you actually tested. And have you considered running ANALYZE on involved tables? Then you can work with pg_class.reltuples ...
  
  – Erwin Brandstetter
  1 hour ago
  

  
  
  
  

add a comment | 

I'm using Postgres 9.6

I'm creating a script that compares two schema which have the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples attribute found in the information schema because it is not guaranteed to be correct.

So far I have this function:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(schema1 text, schema2 text) 

RETURNS TABLE(tablename text, schema1_ct integer, schema2_ct integer, match boolean) AS $$

DECLARE

    schema1_tables CURSOR FOR

        select pg_tables.tablename

        from pg_tables

        where schemaname ~ schema1

        order by tablename;

        schema1_ct int;

        schema2_ct int;

BEGIN

    FOR table_record IN schema1_tables LOOP

        EXECUTE 'SELECT count(*) FROM ' || schema1 || '.' || table_record.tablename INTO schema1_ct

        EXECUTE 'SELECT count(*) FROM ' || schema2 || '.' || table_record.tablename INTO schema2_ct

        RETURN QUERY EXECUTE 'SELECT ''' || table_record.tablename || '''::text,' || schema1_ct || ',' || schema2_ct || ',' (schema1_ct >= 0) = (schema2_ct >= 0)

END LOOP

END; $$ LANGUAGE pgpgsql

But I'm really only interested in the cases where the output's "match" column is false, that is, one table is empty and one table is not empty. How I can I sort my output so that row where match = False are shown first?

postgresql postgresql-9.6 plpgsql

share|improve this question

asked Feb 12 at 18:07

DerekDerek

32

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(schema1 text, schema2 text) 

RETURNS TABLE(tablename text, schema1_ct integer, schema2_ct integer, match boolean) AS $$

DECLARE

    schema1_tables CURSOR FOR

        select pg_tables.tablename

        from pg_tables

        where schemaname ~ schema1

        order by tablename;

        schema1_ct int;

        schema2_ct int;

BEGIN

    FOR table_record IN schema1_tables LOOP

        EXECUTE 'SELECT count(*) FROM ' || schema1 || '.' || table_record.tablename INTO schema1_ct

        EXECUTE 'SELECT count(*) FROM ' || schema2 || '.' || table_record.tablename INTO schema2_ct

        RETURN QUERY EXECUTE 'SELECT ''' || table_record.tablename || '''::text,' || schema1_ct || ',' || schema2_ct || ',' (schema1_ct >= 0) = (schema2_ct >= 0)

END LOOP

END; $$ LANGUAGE pgpgsql

share|improve this question

asked Feb 12 at 18:07

DerekDerek

32

share|improve this question

asked Feb 12 at 18:07

DerekDerek

32

asked Feb 12 at 18:07

DerekDerek

32

asked Feb 12 at 18:07

DerekDerek

32

1 Answer
1

active

oldest

votes

0

But I'm really only interested in the cases where the output's "match" column is false

So why bother with the rest at all? This function does what you need, efficient and safe against SQL injection:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text) 

  RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint)

  LANGUAGE plpgsql AS

$func$

BEGIN

   FOR tablename IN

      SELECT quote_ident(t.tablename)  -- must be table-qualified, due to dupe in OUT parameters!

      FROM   pg_catalog.pg_tables t

      WHERE  t.schemaname = _schema1

      ORDER  BY 1

   LOOP

      EXECUTE format(

         'SELECT (SELECT count(*) FROM %1$I.%3$s)

               , (SELECT count(*) FROM %2$I.%3$s)

          WHERE  EXISTS (SELECT FROM %1$I.%3$s)

              <> EXISTS (SELECT FROM %2$I.%3$s)'

       , _schema1, _schema2, tablename)

      INTO schema1_ct, schema2_ct;



      CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values

      RETURN NEXT;

   END LOOP;

END

$func$;

Call:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');

Counting can be expensive with big tables, so this function only counts when required.

share

answered 6 mins ago

Erwin BrandstetterErwin Brandstetter

93.2k9178292

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229534%2fsort-output-of-plpgsql-script%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

But I'm really only interested in the cases where the output's "match" column is false

So why bother with the rest at all? This function does what you need, efficient and safe against SQL injection:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text) 

  RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint)

  LANGUAGE plpgsql AS

$func$

BEGIN

   FOR tablename IN

      SELECT quote_ident(t.tablename)  -- must be table-qualified, due to dupe in OUT parameters!

      FROM   pg_catalog.pg_tables t

      WHERE  t.schemaname = _schema1

      ORDER  BY 1

   LOOP

      EXECUTE format(

         'SELECT (SELECT count(*) FROM %1$I.%3$s)

               , (SELECT count(*) FROM %2$I.%3$s)

          WHERE  EXISTS (SELECT FROM %1$I.%3$s)

              <> EXISTS (SELECT FROM %2$I.%3$s)'

       , _schema1, _schema2, tablename)

      INTO schema1_ct, schema2_ct;



      CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values

      RETURN NEXT;

   END LOOP;

END

$func$;

Call:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');

Counting can be expensive with big tables, so this function only counts when required.

share

answered 6 mins ago

Erwin BrandstetterErwin Brandstetter

93.2k9178292

add a comment | 

0

But I'm really only interested in the cases where the output's "match" column is false

So why bother with the rest at all? This function does what you need, efficient and safe against SQL injection:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text) 

  RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint)

  LANGUAGE plpgsql AS

$func$

BEGIN

   FOR tablename IN

      SELECT quote_ident(t.tablename)  -- must be table-qualified, due to dupe in OUT parameters!

      FROM   pg_catalog.pg_tables t

      WHERE  t.schemaname = _schema1

      ORDER  BY 1

   LOOP

      EXECUTE format(

         'SELECT (SELECT count(*) FROM %1$I.%3$s)

               , (SELECT count(*) FROM %2$I.%3$s)

          WHERE  EXISTS (SELECT FROM %1$I.%3$s)

              <> EXISTS (SELECT FROM %2$I.%3$s)'

       , _schema1, _schema2, tablename)

      INTO schema1_ct, schema2_ct;



      CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values

      RETURN NEXT;

   END LOOP;

END

$func$;

Call:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');

Counting can be expensive with big tables, so this function only counts when required.

share

answered 6 mins ago

Erwin BrandstetterErwin Brandstetter

93.2k9178292

add a comment | 

But I'm really only interested in the cases where the output's "match" column is false

So why bother with the rest at all? This function does what you need, efficient and safe against SQL injection:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text) 

  RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint)

  LANGUAGE plpgsql AS

$func$

BEGIN

   FOR tablename IN

      SELECT quote_ident(t.tablename)  -- must be table-qualified, due to dupe in OUT parameters!

      FROM   pg_catalog.pg_tables t

      WHERE  t.schemaname = _schema1

      ORDER  BY 1

   LOOP

      EXECUTE format(

         'SELECT (SELECT count(*) FROM %1$I.%3$s)

               , (SELECT count(*) FROM %2$I.%3$s)

          WHERE  EXISTS (SELECT FROM %1$I.%3$s)

              <> EXISTS (SELECT FROM %2$I.%3$s)'

       , _schema1, _schema2, tablename)

      INTO schema1_ct, schema2_ct;



      CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values

      RETURN NEXT;

   END LOOP;

END

$func$;

Call:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');

Counting can be expensive with big tables, so this function only counts when required.

share

answered 6 mins ago

Erwin BrandstetterErwin Brandstetter

93.2k9178292

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text) 

  RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint)

  LANGUAGE plpgsql AS

$func$

BEGIN

   FOR tablename IN

      SELECT quote_ident(t.tablename)  -- must be table-qualified, due to dupe in OUT parameters!

      FROM   pg_catalog.pg_tables t

      WHERE  t.schemaname = _schema1

      ORDER  BY 1

   LOOP

      EXECUTE format(

         'SELECT (SELECT count(*) FROM %1$I.%3$s)

               , (SELECT count(*) FROM %2$I.%3$s)

          WHERE  EXISTS (SELECT FROM %1$I.%3$s)

              <> EXISTS (SELECT FROM %2$I.%3$s)'

       , _schema1, _schema2, tablename)

      INTO schema1_ct, schema2_ct;



      CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values

      RETURN NEXT;

   END LOOP;

END

$func$;

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');

share

answered 6 mins ago

Erwin BrandstetterErwin Brandstetter

93.2k9178292

answered 6 mins ago

Erwin BrandstetterErwin Brandstetter

93.2k9178292

answered 6 mins ago

Erwin BrandstetterErwin Brandstetter

93.2k9178292