Sfrgttk

Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once

Are encryption algorithms with fixed-point free permutations inherently flawed?

Is layered encryption more secure than long passwords?

Why didn't Lorentz conclude that no object can go faster than light?

How should I ship cards?

Does changing "sa" password require a SQL restart (in mixed mode)?

Why is Shelob considered evil?

Taking an academic pseudonym?

Identical projects by students at two different colleges: still plagiarism?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Which was the first story to feature space elevators?

Can "ee" appear in Latin?

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

What dissuades people from lying about where they live in order to reduce state income taxes?

How to write painful torture scenes without being over-the-top

Automated testing of chained Queueable jobs in Salesforce

Sing Baby Shark

Failing PhD, how to go forward?

Can I legally make a website about boycotting a certain company?

How can I portray body horror and still be sensitive to people with disabilities?

How bad is a Computer Science course that doesn't teach Design Patterns?

How do I write a maintainable, fast, compile-time bit-mask in C++?

Why is opening a file faster than reading variable content?

Is Screenshot Time-tracking Common?

PostgreSQL: How to list all stored functions that access specific table

How to drop all of my functions in PostgreSQL?How can I list all user-owned functions?How do I list all columns for a specified tablePostgreSQL: how do I list all triggers that use a specific field?Using the value of a JSON object that is stored in a PostgreSQL JSON arrayHow do I navigate PostgreSQL documentation to find a list of all functions that work on a specific type?Summary over sets of pointsArbitrary queries on n:m relationship, including “all” and “any”How do I list all columns for a specified table that begin with a specific stringHow to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL?Unrolling an entity-matching table

10

2

Introduction:

PostgreSQL database with several hundred of stored functions, including obsolete, not used etc.

Problem

I need to find out all the stored functions that have any relationship to the table X - as I want to change the table structure. Some of them might be not used, so I can't do that just by looking through the code.

The solution I have ATM is running psql's df+ and grepping output, but I'd prefer more database-like solution, i.e. by using information schema. This will definitely be a repetitive task and I'd like to have it nice and clean.

Any suggestions?

postgresql information-schema

share|improve this question

asked Jul 25 '13 at 5:03

Sergey KudriavtsevSergey Kudriavtsev

2781310

migrated from stackoverflow.com Jul 25 '13 at 15:11

This question came from our site for professional and enthusiast programmers.

add a comment | 

10

2

Introduction:

PostgreSQL database with several hundred of stored functions, including obsolete, not used etc.

Problem

I need to find out all the stored functions that have any relationship to the table X - as I want to change the table structure. Some of them might be not used, so I can't do that just by looking through the code.

The solution I have ATM is running psql's df+ and grepping output, but I'd prefer more database-like solution, i.e. by using information schema. This will definitely be a repetitive task and I'd like to have it nice and clean.

Any suggestions?

postgresql information-schema

share|improve this question

asked Jul 25 '13 at 5:03

Sergey KudriavtsevSergey Kudriavtsev

2781310

migrated from stackoverflow.com Jul 25 '13 at 15:11

This question came from our site for professional and enthusiast programmers.

add a comment | 

Introduction:

PostgreSQL database with several hundred of stored functions, including obsolete, not used etc.

Problem

I need to find out all the stored functions that have any relationship to the table X - as I want to change the table structure. Some of them might be not used, so I can't do that just by looking through the code.

The solution I have ATM is running psql's df+ and grepping output, but I'd prefer more database-like solution, i.e. by using information schema. This will definitely be a repetitive task and I'd like to have it nice and clean.

Any suggestions?

postgresql information-schema

share|improve this question

asked Jul 25 '13 at 5:03

Sergey KudriavtsevSergey Kudriavtsev

2781310

share|improve this question

asked Jul 25 '13 at 5:03

Sergey KudriavtsevSergey Kudriavtsev

2781310

share|improve this question

asked Jul 25 '13 at 5:03

Sergey KudriavtsevSergey Kudriavtsev

2781310

asked Jul 25 '13 at 5:03

Sergey KudriavtsevSergey Kudriavtsev

2781310

asked Jul 25 '13 at 5:03

Sergey KudriavtsevSergey Kudriavtsev

2781310

1 Answer
1

active

oldest

votes

14

The body of a function is just stored as string. There is no list of referenced objects. (That's different from views, for instance, where actual links to referenced tables are saved.)

This query for Postgres 10 or older uses the system catalog information function pg_get_functiondef() to reconstruct the CREATE FUNCTION script for relevant functions and searches for the table name with a case-insensitive regular expression:

SELECT n.nspname AS schema_name

     , p.proname AS function_name

     , pg_get_function_arguments(p.oid) AS args

     , pg_get_functiondef(p.oid) AS func_def

FROM   pg_proc p

JOIN   pg_namespace n ON n.oid = p.pronamespace

WHERE  NOT p.proisagg

AND    n.nspname NOT LIKE 'pg_%'

AND    n.nspname <> 'information_schema'

AND    pg_get_functiondef(p.oid) ~* 'mbigM';

It should do the job, but it's obviously not bullet-proof. It can fail for dynamic SQL where the table name is generated dynamically and it can return any number of false positives - especially if the table name is a common word.

Aggregate functions and all functions from system schemas are excluded.

m and M mark the beginning and end of a word in the regular expression.

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. Related:

• How to drop all of my functions in PostgreSQL?

share|improve this answer

edited 8 mins ago

answered Jul 25 '13 at 5:34

Erwin BrandstetterErwin Brandstetter

92.8k9177291

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yep... it's not totally robust, in the sense that it won't find EXECUTE expressions like 'mm_'||name_parameter, and it won't cope correctly with quoted names like "my""table"" or with case-folding, but it'll do most of what most people will want.
  
  – Craig Ringer
  Jul 25 '13 at 5:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @CraigRinger: Yeah, dynamic queries with EXECUTE are almost impossible to cover. But case-folding can be covered with ~* instead of ~ - or any other case-insensitive pattern-matching.
  
  – Erwin Brandstetter
  Jul 25 '13 at 5:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So long as the operator isn't crazy enough to actually create tables named "MyTable" and MyTable, at least... and honestly, that's a "well, that might be allowed but it isn't smart" move.
  
  – Craig Ringer
  Jul 25 '13 at 6:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the answer! I actually don't use dynamic table name construction anywhere and all table names are lowercase.
  
  – Sergey Kudriavtsev
  Jul 25 '13 at 11:16
  

  
  
  
  

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%2f46995%2fpostgresql-how-to-list-all-stored-functions-that-access-specific-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

14

The body of a function is just stored as string. There is no list of referenced objects. (That's different from views, for instance, where actual links to referenced tables are saved.)

This query for Postgres 10 or older uses the system catalog information function pg_get_functiondef() to reconstruct the CREATE FUNCTION script for relevant functions and searches for the table name with a case-insensitive regular expression:

SELECT n.nspname AS schema_name

     , p.proname AS function_name

     , pg_get_function_arguments(p.oid) AS args

     , pg_get_functiondef(p.oid) AS func_def

FROM   pg_proc p

JOIN   pg_namespace n ON n.oid = p.pronamespace

WHERE  NOT p.proisagg

AND    n.nspname NOT LIKE 'pg_%'

AND    n.nspname <> 'information_schema'

AND    pg_get_functiondef(p.oid) ~* 'mbigM';

It should do the job, but it's obviously not bullet-proof. It can fail for dynamic SQL where the table name is generated dynamically and it can return any number of false positives - especially if the table name is a common word.

Aggregate functions and all functions from system schemas are excluded.

m and M mark the beginning and end of a word in the regular expression.

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. Related:

• How to drop all of my functions in PostgreSQL?

share|improve this answer

edited 8 mins ago

answered Jul 25 '13 at 5:34

Erwin BrandstetterErwin Brandstetter

92.8k9177291

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yep... it's not totally robust, in the sense that it won't find EXECUTE expressions like 'mm_'||name_parameter, and it won't cope correctly with quoted names like "my""table"" or with case-folding, but it'll do most of what most people will want.
  
  – Craig Ringer
  Jul 25 '13 at 5:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @CraigRinger: Yeah, dynamic queries with EXECUTE are almost impossible to cover. But case-folding can be covered with ~* instead of ~ - or any other case-insensitive pattern-matching.
  
  – Erwin Brandstetter
  Jul 25 '13 at 5:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So long as the operator isn't crazy enough to actually create tables named "MyTable" and MyTable, at least... and honestly, that's a "well, that might be allowed but it isn't smart" move.
  
  – Craig Ringer
  Jul 25 '13 at 6:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the answer! I actually don't use dynamic table name construction anywhere and all table names are lowercase.
  
  – Sergey Kudriavtsev
  Jul 25 '13 at 11:16
  

  
  
  
  

add a comment | 

14

The body of a function is just stored as string. There is no list of referenced objects. (That's different from views, for instance, where actual links to referenced tables are saved.)

This query for Postgres 10 or older uses the system catalog information function pg_get_functiondef() to reconstruct the CREATE FUNCTION script for relevant functions and searches for the table name with a case-insensitive regular expression:

SELECT n.nspname AS schema_name

     , p.proname AS function_name

     , pg_get_function_arguments(p.oid) AS args

     , pg_get_functiondef(p.oid) AS func_def

FROM   pg_proc p

JOIN   pg_namespace n ON n.oid = p.pronamespace

WHERE  NOT p.proisagg

AND    n.nspname NOT LIKE 'pg_%'

AND    n.nspname <> 'information_schema'

AND    pg_get_functiondef(p.oid) ~* 'mbigM';

It should do the job, but it's obviously not bullet-proof. It can fail for dynamic SQL where the table name is generated dynamically and it can return any number of false positives - especially if the table name is a common word.

Aggregate functions and all functions from system schemas are excluded.

m and M mark the beginning and end of a word in the regular expression.

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. Related:

• How to drop all of my functions in PostgreSQL?

share|improve this answer

edited 8 mins ago

answered Jul 25 '13 at 5:34

Erwin BrandstetterErwin Brandstetter

92.8k9177291

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yep... it's not totally robust, in the sense that it won't find EXECUTE expressions like 'mm_'||name_parameter, and it won't cope correctly with quoted names like "my""table"" or with case-folding, but it'll do most of what most people will want.
  
  – Craig Ringer
  Jul 25 '13 at 5:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @CraigRinger: Yeah, dynamic queries with EXECUTE are almost impossible to cover. But case-folding can be covered with ~* instead of ~ - or any other case-insensitive pattern-matching.
  
  – Erwin Brandstetter
  Jul 25 '13 at 5:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So long as the operator isn't crazy enough to actually create tables named "MyTable" and MyTable, at least... and honestly, that's a "well, that might be allowed but it isn't smart" move.
  
  – Craig Ringer
  Jul 25 '13 at 6:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the answer! I actually don't use dynamic table name construction anywhere and all table names are lowercase.
  
  – Sergey Kudriavtsev
  Jul 25 '13 at 11:16
  

  
  
  
  

add a comment | 

The body of a function is just stored as string. There is no list of referenced objects. (That's different from views, for instance, where actual links to referenced tables are saved.)

This query for Postgres 10 or older uses the system catalog information function pg_get_functiondef() to reconstruct the CREATE FUNCTION script for relevant functions and searches for the table name with a case-insensitive regular expression:

SELECT n.nspname AS schema_name

     , p.proname AS function_name

     , pg_get_function_arguments(p.oid) AS args

     , pg_get_functiondef(p.oid) AS func_def

FROM   pg_proc p

JOIN   pg_namespace n ON n.oid = p.pronamespace

WHERE  NOT p.proisagg

AND    n.nspname NOT LIKE 'pg_%'

AND    n.nspname <> 'information_schema'

AND    pg_get_functiondef(p.oid) ~* 'mbigM';

It should do the job, but it's obviously not bullet-proof. It can fail for dynamic SQL where the table name is generated dynamically and it can return any number of false positives - especially if the table name is a common word.

Aggregate functions and all functions from system schemas are excluded.

m and M mark the beginning and end of a word in the regular expression.

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. Related:

• How to drop all of my functions in PostgreSQL?

share|improve this answer

edited 8 mins ago

answered Jul 25 '13 at 5:34

Erwin BrandstetterErwin Brandstetter

92.8k9177291

SELECT n.nspname AS schema_name

     , p.proname AS function_name

     , pg_get_function_arguments(p.oid) AS args

     , pg_get_functiondef(p.oid) AS func_def

FROM   pg_proc p

JOIN   pg_namespace n ON n.oid = p.pronamespace

WHERE  NOT p.proisagg

AND    n.nspname NOT LIKE 'pg_%'

AND    n.nspname <> 'information_schema'

AND    pg_get_functiondef(p.oid) ~* 'mbigM';

share|improve this answer

edited 8 mins ago

answered Jul 25 '13 at 5:34

Erwin BrandstetterErwin Brandstetter

92.8k9177291

answered Jul 25 '13 at 5:34

Erwin BrandstetterErwin Brandstetter

92.8k9177291

answered Jul 25 '13 at 5:34

Erwin BrandstetterErwin Brandstetter

92.8k9177291