X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 85E5F9DC806 for ; Sun, 19 Mar 2006 09:26:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16936-03 for ; Sun, 19 Mar 2006 09:26:22 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 2771C9DCB6A for ; Sun, 19 Mar 2006 09:26:17 -0400 (AST) Received: from cicero2.cybercity.dk (cicero2.cybercity.dk [212.242.40.53]) by svr4.postgresql.org (Postfix) with ESMTP id 8F8AB5AF9B0 for ; Sun, 19 Mar 2006 13:26:21 +0000 (GMT) Received: from user3.cybercity.dk (user3.cybercity.dk [212.242.41.36]) by cicero2.cybercity.dk (Postfix) with ESMTP id 8B60624543E for ; Sun, 19 Mar 2006 14:26:17 +0100 (CET) Received: from [192.168.0.79] (0x3e42eeca.adsl.cybercity.dk [62.66.238.202]) by user3.cybercity.dk (Postfix) with ESMTP id 8254C93C29 for ; Sun, 19 Mar 2006 14:26:16 +0100 (CET) Message-ID: <441D5C14.8010204@krap.dk> Date: Sun, 19 Mar 2006 14:26:44 +0100 From: Svenne Krap User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: How to call table returning function with other table Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms090401000605020701000205" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200603/274 X-Sequence-Number: 24620 This is a cryptographically signed message in MIME format. --------------ms090401000605020701000205 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi. I have a function that is defined like (which works, sorry for the pseudocode) create type xxx as (id,...); create function calcuate_xxx (integer) returns xxx as $$ select $1, (select sum(amount) from bigtable where something) as a, (select sum(amount) from bigtable where some-other-thing) as b $$ language sql stable; What I cannot figure out is, if I have another query that returns a single column of those id's, how do I get a table back with the function applied to all ids (hence multiple rows). So it is something like "select xxx(id) from othertable where otherwhere = 't'" except that it mangles the columns into an array. I have tried to move the function-call into the fromlist (as it is usually done) but I seem unsuccessful in getting the ID's into it. Thanks in advance Svenne --------------ms090401000605020701000205 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIKTjCC BSMwggQLoAMCAQICBD/KpecwDQYJKoZIhvcNAQEFBQAwMTELMAkGA1UEBhMCREsxDDAKBgNV BAoTA1REQzEUMBIGA1UEAxMLVERDIE9DRVMgQ0EwHhcNMDUwMjIzMTMxMTMwWhcNMDcwMjIz MTM0MTMwWjBzMQswCQYDVQQGEwJESzEpMCcGA1UEChMgSW5nZW4gb3JnYW5pc2F0b3Jpc2sg dGlsa255dG5pbmcxOTASBgNVBAMTC1N2ZW5uZSBLcmFwMCMGA1UEBRMcUElEOjk4MDItMjAw Mi0yLTk3OTMxMTk0MjAyNzCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEApF423aqlHV/8 RiX5vX5QezMEXKixU6KUSUQOfERsFgahaUn5LobNfxB31+5nKVH3EJfK/UpcE4Alfsa87+hX MBRzaAv5jbwh57NFSNcmFD/wgcIYupvyWhASacjcBnBfiqNuDBVIT7wL42yijfM3LSVjJOPw /c5R5wwFfKqjKzMCAwEAAaOCAoMwggJ/MA4GA1UdDwEB/wQEAwID+DArBgNVHRAEJDAigA8y MDA1MDIyMzEzMTEzMFqBDzIwMDcwMjIzMTM0MTMwWjCCATcGA1UdIASCAS4wggEqMIIBJgYK KoFQgSkBAQEBAjCCARYwLwYIKwYBBQUHAgEWI2h0dHA6Ly93d3cuY2VydGlmaWthdC5kay9y ZXBvc2l0b3J5MIHiBggrBgEFBQcCAjCB1TAKFgNUREMwAwIBARqBxkZvciBhbnZlbmRlbHNl IGFmIGNlcnRpZmlrYXRldCBn5mxkZXIgT0NFUyB2aWxr5XIsIENQUyBvZyBPQ0VTIENQLCBk ZXIga2FuIGhlbnRlcyBmcmEgd3d3LmNlcnRpZmlrYXQuZGsvcmVwb3NpdG9yeS4gQmVt5nJr LCBhdCBUREMgZWZ0ZXIgdmlsa+VyZW5lIGhhciBldCBiZWdy5m5zZXQgYW5zdmFyIGlmdC4g cHJvZmVzc2lvbmVsbGUgcGFydGVyLjAZBgNVHREEEjAQgQ5zdmVubmVAa3JhcC5kazCBgwYD VR0fBHwwejBKoEigRqREMEIxCzAJBgNVBAYTAkRLMQwwCgYDVQQKEwNUREMxFDASBgNVBAMT C1REQyBPQ0VTIENBMQ8wDQYDVQQDEwZDUkw1NjgwLKAqoCiGJmh0dHA6Ly9jcmwub2Nlcy5j ZXJ0aWZpa2F0LmRrL29jZXMuY3JsMB8GA1UdIwQYMBaAFGC1hexWZH4SGSdnHVAVS3OuO/kS MB0GA1UdDgQWBBTmRrmEbRpZf+Pt0S/qphnyMgKoRzAJBgNVHRMEAjAAMBkGCSqGSIb2fQdB AAQMMAobBFY3LjEDAgOoMA0GCSqGSIb3DQEBBQUAA4IBAQBgfOEd/VkXc6lFNItNvhgrbPc3 eNcGE7l6sbaG1gYLREn4xmmYajtP90INRG3OlNm3ZlVeYxaMbzQKVlcrKMEwA7cMZIMcT97t KjXabwqCc/0jNCPceO/ZPoUc7T3fuF7B1QjJelzXsoK4av2lDDgaAJL8WiExsGYmJ0BwfNLZ YjLRwJ+Db4gsmvjTFazbkRtzqqqAzjy2WC6L4WEuS6EUmDhdX/ZFdq0bT58lx++DKo+TuDu3 bkIi46l6yV5lI4CgBRTmahRyyCuUMjDUiLvgX1m3Ys/U5c88MDgwrKtLcXy7TyCEazOLVC5z vAgKvNjbdSQjkFZEDiA/oVtwGNH6MIIFIzCCBAugAwIBAgIEP8ql5zANBgkqhkiG9w0BAQUF ADAxMQswCQYDVQQGEwJESzEMMAoGA1UEChMDVERDMRQwEgYDVQQDEwtUREMgT0NFUyBDQTAe Fw0wNTAyMjMxMzExMzBaFw0wNzAyMjMxMzQxMzBaMHMxCzAJBgNVBAYTAkRLMSkwJwYDVQQK EyBJbmdlbiBvcmdhbmlzYXRvcmlzayB0aWxrbnl0bmluZzE5MBIGA1UEAxMLU3Zlbm5lIEty YXAwIwYDVQQFExxQSUQ6OTgwMi0yMDAyLTItOTc5MzExOTQyMDI3MIGfMA0GCSqGSIb3DQEB AQUAA4GNADCBiQKBgQCkXjbdqqUdX/xGJfm9flB7MwRcqLFTopRJRA58RGwWBqFpSfkuhs1/ EHfX7mcpUfcQl8r9SlwTgCV+xrzv6FcwFHNoC/mNvCHns0VI1yYUP/CBwhi6m/JaEBJpyNwG cF+Ko24MFUhPvAvjbKKN8zctJWMk4/D9zlHnDAV8qqMrMwIDAQABo4ICgzCCAn8wDgYDVR0P AQH/BAQDAgP4MCsGA1UdEAQkMCKADzIwMDUwMjIzMTMxMTMwWoEPMjAwNzAyMjMxMzQxMzBa MIIBNwYDVR0gBIIBLjCCASowggEmBgoqgVCBKQEBAQECMIIBFjAvBggrBgEFBQcCARYjaHR0 cDovL3d3dy5jZXJ0aWZpa2F0LmRrL3JlcG9zaXRvcnkwgeIGCCsGAQUFBwICMIHVMAoWA1RE QzADAgEBGoHGRm9yIGFudmVuZGVsc2UgYWYgY2VydGlmaWthdGV0IGfmbGRlciBPQ0VTIHZp bGvlciwgQ1BTIG9nIE9DRVMgQ1AsIGRlciBrYW4gaGVudGVzIGZyYSB3d3cuY2VydGlmaWth dC5kay9yZXBvc2l0b3J5LiBCZW3mcmssIGF0IFREQyBlZnRlciB2aWxr5XJlbmUgaGFyIGV0 IGJlZ3LmbnNldCBhbnN2YXIgaWZ0LiBwcm9mZXNzaW9uZWxsZSBwYXJ0ZXIuMBkGA1UdEQQS MBCBDnN2ZW5uZUBrcmFwLmRrMIGDBgNVHR8EfDB6MEqgSKBGpEQwQjELMAkGA1UEBhMCREsx DDAKBgNVBAoTA1REQzEUMBIGA1UEAxMLVERDIE9DRVMgQ0ExDzANBgNVBAMTBkNSTDU2ODAs oCqgKIYmaHR0cDovL2NybC5vY2VzLmNlcnRpZmlrYXQuZGsvb2Nlcy5jcmwwHwYDVR0jBBgw FoAUYLWF7FZkfhIZJ2cdUBVLc647+RIwHQYDVR0OBBYEFOZGuYRtGll/4+3RL+qmGfIyAqhH MAkGA1UdEwQCMAAwGQYJKoZIhvZ9B0EABAwwChsEVjcuMQMCA6gwDQYJKoZIhvcNAQEFBQAD ggEBAGB84R39WRdzqUU0i02+GCts9zd41wYTuXqxtobWBgtESfjGaZhqO0/3Qg1Ebc6U2bdm VV5jFoxvNApWVysowTADtwxkgxxP3u0qNdpvCoJz/SM0I9x479k+hRztPd+4XsHVCMl6XNey grhq/aUMOBoAkvxaITGwZiYnQHB80tliMtHAn4NviCya+NMVrNuRG3OqqoDOPLZYLovhYS5L oRSYOF1f9kV2rRtPnyXH74Mqj5O4O7duQiLjqXrJXmUjgKAFFOZqFHLIK5QyMNSIu+BfWbdi z9TlzzwwODCsq0txfLtPIIRrM4tULnO8CAq82Nt1JCOQVkQOID+hW3AY0foxggIqMIICJgIB ATA5MDExCzAJBgNVBAYTAkRLMQwwCgYDVQQKEwNUREMxFDASBgNVBAMTC1REQyBPQ0VTIENB AgQ/yqXnMAkGBSsOAwIaBQCgggFHMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZI hvcNAQkFMQ8XDTA2MDMxOTEzMjY0NVowIwYJKoZIhvcNAQkEMRYEFMEbsE01ev6/IxPW8+Ak oSXc5o4GMEgGCSsGAQQBgjcQBDE7MDkwMTELMAkGA1UEBhMCREsxDDAKBgNVBAoTA1REQzEU MBIGA1UEAxMLVERDIE9DRVMgQ0ECBD/KpecwSgYLKoZIhvcNAQkQAgsxO6A5MDExCzAJBgNV BAYTAkRLMQwwCgYDVQQKEwNUREMxFDASBgNVBAMTC1REQyBPQ0VTIENBAgQ/yqXnMFIGCSqG SIb3DQEJDzFFMEMwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFA MAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMA0GCSqGSIb3DQEBAQUABIGAbB16U40clONp+Fl/ tBf0JcOdPsfo50qMiYlZcu1mhHoC10HmJd0Mhs6XlibwVmsVuGsKwSXEFp2ZCNRJej5DfNUJ o3NlHavyC/Ux8axEJ6laij6A/lwOYAzUo7iuCzmiSYpvMNnBVDw8wXTyHJFQmSdjXi2i4xjZ FOl5GdRhrx8AAAAAAAA= --------------ms090401000605020701000205--