Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tDQSY-000cR8-I1 for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 15:48:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tDQSV-00E8PP-Og for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 15:48:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tDQSV-00E8PH-7I for pgsql-general@lists.postgresql.org; Tue, 19 Nov 2024 15:48:07 +0000 Received: from mail-ot1-x32e.google.com ([2607:f8b0:4864:20::32e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tDQSS-002iff-AY for pgsql-general@postgresql.org; Tue, 19 Nov 2024 15:48:05 +0000 Received: by mail-ot1-x32e.google.com with SMTP id 46e09a7af769-71a5ab612ceso525528a34.0 for ; Tue, 19 Nov 2024 07:48:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732031283; x=1732636083; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=5RWnAG7k3EFg9XEH8MWOQAcnt6MNIXWZQddERSDEqfc=; b=Xxl3P7pMIiVDmlxfUaLBk/pP/MnB93EJlOC0kmMDqgVWxK+rJFSkLy4HNKa1CbI7qB v0MYQSuBFesh2jAiSwgJVkl1WJz4Kw8spsmYTuFIAE/YqY7zQNZYTRTzbFojSqwlz8zm HlqYkhbC6kbBeoX8XFQzimsNUNRwzPZ+CBM0Wd2KgUGRVDYC9yvi4LE4DTvJlFVZyFRe LgULn3YMvhbUVPS3+o2OK7KAz6AZER8VbwkrG+0GDj/0JyfH4Il7eGwN6D1WgqF6zeHl bJaZzC+gueXq2Uz3wCn8df0YAkyLMmdB2gXH5n6vnlMKVsB7hHZVE1+jxuLHoW2gC6XY 6gaQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732031283; x=1732636083; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=5RWnAG7k3EFg9XEH8MWOQAcnt6MNIXWZQddERSDEqfc=; b=v6qPSIcQkteCam3qn66nMOs6bs0irXVTmRb6Lv3fgy0iBfuZGjgQfQx4MHmMp7DHsq 3o6jxMxYT0e7iPj/t6I7x0xEhbHgVVvtw+GjEPRrjF2kB+O1svnReCgkMWooDOXD0KkX LaNDu8ani3dCrDy9io/2ZeGwC4VMXQZaPbH51PmTAaczeZVn3ANwqMx3iMzwIntN4hBv 75tELyXIy+mTgxUxcYx3ri4Jy2m5RUVz6KMAxCT6k1FMb+VjTENO5YY5QeW/laGQa/Wv +GM2N6FSN4Mzr8UMhQtnicGtLaOCYTlEKuGghA05SM554VILJYLvHZhhDGp/8WtCcuyt 9E3Q== X-Gm-Message-State: AOJu0YyxHuq1g4To6aIVVGcTTOVEB5xxiXr0g3vXK6LDXNBG4klN9gUU rVy++D1TfFDtK0jamZts1t2cpNt+qXqNfhWKQv/OAeIagI/emEmicVm9PRXzKU40CDYjMezeiqI hrmzyfUbFaMENPokzuIyocqsJ5I5kVceK X-Google-Smtp-Source: AGHT+IHWRRs6L3sEOmieEaDhr8vAdZMiPT5YbY60YZzgAMECT1eGGtuYiyPfvInf+Ii9NZQcuJXqxnyRom6tSnLJ6rM= X-Received: by 2002:a05:6830:650a:b0:71a:61b1:55bb with SMTP id 46e09a7af769-71a7797b858mr14371759a34.17.1732031283430; Tue, 19 Nov 2024 07:48:03 -0800 (PST) MIME-Version: 1.0 From: Ron Johnson Date: Tue, 19 Nov 2024 10:47:52 -0500 Message-ID: Subject: Specifying columns returned by a function, when the function is in a SELECT column list? To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000638cfb062745f548" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000638cfb062745f548 Content-Type: text/plain; charset="UTF-8" It's trivial to specify columns when a table-returning function is the FROM clause, but how does one specify columns when the table-returning function is a column in a SELECT clause? I don't have the proper vocabulary to know what to Google for. Examples below: Easy: cdsbmop=# select downstream_table, downstream_column, downstream_index cdsbmop-# from dba.get_fk_referenced_by('cdsschema.retention_policy_code'); downstream_table | downstream_column | downstream_index ---------------------------------------+--------------------------+----------------------------------------------------- cdsschema.bank_item_type_ret | retention_policy_code_id | ALERT!!! MISSING INDEX cdsschema.bank_item_type_ret_hist | retention_policy_code_id | ALERT!!! MISSING INDEX cdsschema.customer_item_type_ret | retention_policy_code_id | idx_customer_item_type_ret_retention_policy_code_id cdsschema.customer_item_type_ret_hist | retention_policy_code_id | ALERT!!! MISSING INDEX cdsschema.ex_data_partition_policy | retention_policy_code_id | ALERT!!! MISSING INDEX cdsschema.lockbox_item_type_ret | retention_policy_code_id | idx_lockbox_item_type_ret_retention_policy_code_id cdsschema.lockbox_item_type_ret_hist | retention_policy_code_id | ALERT!!! MISSING INDEX (7 rows) cdsbmop=# select table_name from dba.latest_vacuum_and_analyze where table_name = 'retention_policy_code'; table_name ----------------------- retention_policy_code (1 row) Here, I want to only specify some of the dba.get_fk_referenced_by() columns: cdsbmop=# select dba.get_fk_referenced_by('cdsschema.'||table_name) from dba.latest_vacuum_and_analyze where table_name = 'retention_policy_code'; get_fk_referenced_by ------------------------------------------------------------------------------------------------------------------------------------------ (retention_policy_code_id,cdsschema.bank_item_type_ret,retention_policy_code_id,"ALERT!!! MISSING INDEX") (retention_policy_code_id,cdsschema.bank_item_type_ret_hist,retention_policy_code_id,"ALERT!!! MISSING INDEX") (retention_policy_code_id,cdsschema.customer_item_type_ret,retention_policy_code_id,idx_customer_item_type_ret_retention_policy_code_id) (retention_policy_code_id,cdsschema.customer_item_type_ret_hist,retention_policy_code_id,"ALERT!!! MISSING INDEX") (retention_policy_code_id,cdsschema.ex_data_partition_policy,retention_policy_code_id,"ALERT!!! MISSING INDEX") (retention_policy_code_id,cdsschema.lockbox_item_type_ret,retention_policy_code_id,idx_lockbox_item_type_ret_retention_policy_code_id) (retention_policy_code_id,cdsschema.lockbox_item_type_ret_hist,retention_policy_code_id,"ALERT!!! MISSING INDEX") (7 rows) -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000638cfb062745f548 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
It's trivial to specify columns when a table-retu= rning function is the FROM clause, but how does one specify columns when th= e table-returning function is a column in a SELECT clause?

I don't have the proper vocabulary to know what to Google for.=

Examples below:

Easy:
cdsbmop=3D# select downstream_table, downs= tream_column, downstream_index
cdsbmop-# from dba.get_fk_referenced_by(= 'cdsschema.retention_policy_code');
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0downstream_table =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0downstream_column =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0downstream_index =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
--------------------------------= -------+--------------------------+----------------------------------------= -------------
=C2=A0cdsschema.bank_item_type_ret =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| retention_policy_code_id | ALERT!!! MISSING INDEX
=C2=A0c= dsschema.bank_item_type_ret_hist =C2=A0 =C2=A0 | retention_policy_code_id |= ALERT!!! MISSING INDEX
=C2=A0cdsschema.customer_item_type_ret =C2=A0 = =C2=A0 =C2=A0| retention_policy_code_id | idx_customer_item_type_ret_retent= ion_policy_code_id
=C2=A0cdsschema.customer_item_type_ret_hist | retenti= on_policy_code_id | ALERT!!! MISSING INDEX
=C2=A0cdsschema.ex_data_parti= tion_policy =C2=A0 =C2=A0| retention_policy_code_id | ALERT!!! MISSING INDE= X
=C2=A0cdsschema.lockbox_item_type_ret =C2=A0 =C2=A0 =C2=A0 | retention= _policy_code_id | idx_lockbox_item_type_ret_retention_policy_code_id
=C2= =A0cdsschema.lockbox_item_type_ret_hist =C2=A0| retention_policy_code_id | = ALERT!!! MISSING INDEX
(7 rows)

c= dsbmop=3D# select table_name
from dba.latest_vacuum_and_analyze
wher= e table_name =3D 'retention_policy_code';
=C2=A0 =C2=A0 =C2=A0 t= able_name =C2=A0 =C2=A0 =C2=A0
-----------------------
=C2=A0retenti= on_policy_code
(1 row)

Here, I want to only specify some o= f the=C2=A0dba.get_fk_referenced_by() columns:

cdsbmop=3D# select dba.get_fk_referenced_by('cdsschema.'||table= _name)
from dba.latest_vacuum_and_analyze where table_name =3D 'rete= ntion_policy_code';
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0get_fk_referenced_by =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0
---------------------------------------------------------= ---------------------------------------------------------------------------= ------
=C2=A0(retention_policy_code_id,cdsschema.bank_item_type_ret,rete= ntion_policy_code_id,"ALERT!!! MISSING INDEX")
=C2=A0(retentio= n_policy_code_id,cdsschema.bank_item_type_ret_hist,retention_policy_code_id= ,"ALERT!!! MISSING INDEX")
=C2=A0(retention_policy_code_id,cds= schema.customer_item_type_ret,retention_policy_code_id,idx_customer_item_ty= pe_ret_retention_policy_code_id)
=C2=A0(retention_policy_code_id,cdssche= ma.customer_item_type_ret_hist,retention_policy_code_id,"ALERT!!! MISS= ING INDEX")
=C2=A0(retention_policy_code_id,cdsschema.ex_data_parti= tion_policy,retention_policy_code_id,"ALERT!!! MISSING INDEX")=C2=A0(retention_policy_code_id,cdsschema.lockbox_item_type_ret,retention_= policy_code_id,idx_lockbox_item_type_ret_retention_policy_code_id)
=C2= =A0(retention_policy_code_id,cdsschema.lockbox_item_type_ret_hist,retention= _policy_code_id,"ALERT!!! MISSING INDEX")
(7 rows)
<= /div>


-= -
Death to <Redacted>, and butter sauc= e.
Don't boil me, I'm still alive.
<Redacted>= ; lobster!
--000000000000638cfb062745f548--