Received: from ural2.hszk.bme.hu (ural2.hszk.bme.hu [152.66.130.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8I1Bqd59645 for ; Mon, 17 Sep 2001 21:11:52 -0400 (EDT) (envelope-from kb136@hszk.bme.hu) Received: (from kb136@localhost) by ural2.hszk.bme.hu (8.11.3/8.11.3) id f8I1Bne21957; Tue, 18 Sep 2001 03:11:49 +0200 (MEST) Date: Tue, 18 Sep 2001 03:11:49 +0200 (MEST) From: Kovacs Baldvin X-X-Sender: To: Julester cc: Subject: Re: Checking for table existence In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: MULTIPART/MIXED; BOUNDARY="-559023410-851401618-1000775509=:16528" X-Archive-Number: 200109/120 X-Sequence-Number: 4791 This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. Send mail to mime@docserver.cac.washington.edu for more info. ---559023410-851401618-1000775509=:16528 Content-Type: TEXT/PLAIN; charset=US-ASCII > Hi everyone. In my old SQL Server days, I used a command such as "IF > exists(select name from sys_objects where name = 'xyztable')" to check if a As far as I know, there is not direct support of this. However, I also would appreciate a builtin qexec(text) procedure, for making queries. Now I present a workaround for this probably missing functionality. Developers, if this functionality is included, please let me know. --------------------------------- 1. First, suppose that we have a function, called qexec, which runs the given text parameter as an sql query, and returns the int, which it got from the backend. In this situation, your problem can be solved this way: SELECT CASE WHEN NOT yourtablename IN (your nice select from pg_blabla) THEN qexec('CREATE TABLE (as you like it)') END; ----------------------------------- 2. Now the only thing left is to define the qexec procedure. 2/a. If you are a C programmer: Then try the way presented in the documentation. I included the relating section from my somewhat oldie documentation, please search the same in the current by grepping about a bit. 2/b. If you are not so brave: You can try for example pltcl. Issue these commands As postgres superuser, the path replaced to yours: create function pltcl_call_handler() returns opaque as '/usr/local/pgsql/lib/pltcl.so' language 'C'; create trusted procedural language 'pltcl' handler pltcl_call_handler lancompiler 'Pl/pltcl'; As any user: create function qexec(text) returns int as ' return [spi_exec [ quote $1 ]] ' language 'pltcl'; Now try, what you've done: select qexec('select 1=1'); You should get 1. -------------------------------------------- Here you are. If anybody knows a much simpler solution, please let me know. If it helped or not, let me know also. Regards, Baldvin ---559023410-851401618-1000775509=:16528 Content-Type: TEXT/html; name="spi21548.htm" Content-Transfer-Encoding: BASE64 Content-ID: Content-Description: Content-Disposition: attachment; filename="spi21548.htm" PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDQuMCBU cmFuc2l0aW9uYWwvL0VOIj4NCjxIVE1MDQo+PEhFQUQNCj48VElUTEUNCj5F eGFtcGxlczwvVElUTEUNCj48TUVUQQ0KTkFNRT0iR0VORVJBVE9SIg0KQ09O VEVOVD0iTW9kdWxhciBEb2NCb29rIEhUTUwgU3R5bGVzaGVldCB2ZXJzaW9u IDEuMTgiPjxMSU5LDQpSRUw9IkhPTUUiDQpUSVRMRT0iUG9zdGdyZVNRTCIN CkhSRUY9InBvc3RncmVzLmh0bSI+PExJTksNClJFTD0iVVAiDQpUSVRMRT0i U2VydmVyIFByb2dyYW1taW5nIEludGVyZmFjZSINCkhSRUY9InNwaS5odG0i PjxMSU5LDQpSRUw9IlBSRVZJT1VTIg0KVElUTEU9IlZpc2liaWxpdHkgb2Yg RGF0YSBDaGFuZ2VzIg0KSFJFRj0ic3BpMjE1NDMuaHRtIj48TElOSw0KUkVM PSJORVhUIg0KVElUTEU9IlByb2NlZHVyYWwgTGFuZ3VhZ2VzIg0KSFJFRj0i eHBsYW5nLmh0bSI+PC9IRUFEDQo+PEJPRFkNCkJHQ09MT1I9IiNGRkZGRkYi DQpURVhUPSIjMDAwMDAwIg0KPjxESVYNCkNMQVNTPSJOQVZIRUFERVIiDQo+ PFRBQkxFDQpXSURUSD0iMTAwJSINCkJPUkRFUj0iMCINCkNFTExQQURESU5H PSIwIg0KQ0VMTFNQQUNJTkc9IjAiDQo+PFRSDQo+PFRIDQpDT0xTUEFOPSIz Ig0KQUxJR049ImNlbnRlciINCj5Qb3N0Z3JlU1FMPC9USA0KPjwvVFINCj48 VFINCj48VEQNCldJRFRIPSIxMCUiDQpBTElHTj0ibGVmdCINClZBTElHTj0i Ym90dG9tIg0KPjxBDQpIUkVGPSJzcGkyMTU0My5odG0iDQo+UHJldjwvQQ0K PjwvVEQNCj48VEQNCldJRFRIPSI4MCUiDQpBTElHTj0iY2VudGVyIg0KVkFM SUdOPSJib3R0b20iDQo+Q2hhcHRlciA0OC4gU2VydmVyIFByb2dyYW1taW5n IEludGVyZmFjZTwvVEQNCj48VEQNCldJRFRIPSIxMCUiDQpBTElHTj0icmln aHQiDQpWQUxJR049ImJvdHRvbSINCj48QQ0KSFJFRj0ieHBsYW5nLmh0bSIN Cj5OZXh0PC9BDQo+PC9URA0KPjwvVFINCj48L1RBQkxFDQo+PEhSDQpBTElH Tj0iTEVGVCINCldJRFRIPSIxMDAlIj48L0RJVg0KPjxIMQ0KQ0xBU1M9IlNF Q1QxIg0KPjxBDQpOQU1FPSJBRU4yMTU0OCINCj5FeGFtcGxlczwvQQ0KPjwv SDENCj48UA0KPiAgIFRoaXMgZXhhbXBsZSBvZiBTUEkgdXNhZ2UgZGVtb25z dHJhdGVzIHRoZSB2aXNpYmlsaXR5IHJ1bGUuDQogICBUaGVyZSBhcmUgbW9y ZSBjb21wbGV4IGV4YW1wbGVzIGluIGluIHNyYy90ZXN0L3JlZ3Jlc3MvcmVn cmVzcy5jIGFuZA0KaW4gY29udHJpYi9zcGkuPC9QDQo+PFANCj4gICBUaGlz IGlzIGEgdmVyeSBzaW1wbGUgZXhhbXBsZSBvZiBTUEkgdXNhZ2UuIFRoZSBw cm9jZWR1cmUgZXhlY3EgYWNjZXB0cw0KYW4gU1FMLXF1ZXJ5IGluIGl0cyBm aXJzdCBhcmd1bWVudCBhbmQgdGNvdW50IGluIGl0cyBzZWNvbmQsIGV4ZWN1 dGVzIHRoZQ0KcXVlcnkgdXNpbmcgU1BJX2V4ZWMgYW5kIHJldHVybnMgdGhl IG51bWJlciBvZiB0dXBsZXMgZm9yIHdoaWNoIHRoZSBxdWVyeQ0KZXhlY3V0 ZWQ6DQoNCjxQUkUNCkNMQVNTPSJQUk9HUkFNTElTVElORyINCj4jaW5jbHVk ZSAiZXhlY3V0b3Ivc3BpLmgiCS8qIHRoaXMgaXMgd2hhdCB5b3UgbmVlZCB0 byB3b3JrIHdpdGggU1BJICovDQoNCmludCBleGVjcSh0ZXh0ICpzcWwsIGlu dCBjbnQpOw0KDQppbnQNCmV4ZWNxKHRleHQgKnNxbCwgaW50IGNudCkNCnsN CglpbnQgcmV0Ow0KCWludCBwcm9jID0gMDsNCgkNCglTUElfY29ubmVjdCgp Ow0KCQ0KCXJldCA9IFNQSV9leGVjKHRleHRvdXQoc3FsKSwgY250KTsNCgkN Cglwcm9jID0gU1BJX3Byb2Nlc3NlZDsNCgkvKg0KCSAqIElmIHRoaXMgaXMg U0VMRUNUIGFuZCBzb21lIHR1cGxlKHMpIGZldGNoZWQgLQ0KCSAqIHJldHVy bnMgdHVwbGVzIHRvIHRoZSBjYWxsZXIgdmlhIGVsb2cgKE5PVElDRSkuDQoJ ICovDQoJaWYgKCByZXQgPT0gU1BJX09LX1NFTEVDVCAmIzM4OyYjMzg7IFNQ SV9wcm9jZXNzZWQgJiM2MjsgMCApDQoJew0KCQlUdXBsZURlc2MgdHVwZGVz YyA9IFNQSV90dXB0YWJsZS0mIzYyO3R1cGRlc2M7DQoJCVNQSVR1cGxlVGFi bGUgKnR1cHRhYmxlID0gU1BJX3R1cHRhYmxlOw0KCQljaGFyIGJ1Zls4MTky XTsNCgkJaW50IGk7DQoJCQ0KCQlmb3IgKHJldCA9IDA7IHJldCAmIzYwOyBw cm9jOyByZXQrKykNCgkJew0KCQkJSGVhcFR1cGxlIHR1cGxlID0gdHVwdGFi bGUtJiM2Mjt2YWxzW3JldF07DQoJCQkNCgkJCWZvciAoaSA9IDEsIGJ1Zlsw XSA9IDA7IGkgJiM2MDs9IHR1cGRlc2MtJiM2MjtuYXR0czsgaSsrKQ0KCQkJ CXNwcmludGYoYnVmICsgc3RybGVuIChidWYpLCAiICVzJXMiLA0KCQkJCQlT UElfZ2V0dmFsdWUodHVwbGUsIHR1cGRlc2MsIGkpLA0KCQkJCQkoaSA9PSB0 dXBkZXNjLSYjNjI7bmF0dHMpID8gIiAiIDogIiB8Iik7DQoJCQllbG9nIChO T1RJQ0UsICJFWEVDUTogJXMiLCBidWYpOw0KCQl9DQoJfQ0KDQoJU1BJX2Zp bmlzaCgpOw0KDQoJcmV0dXJuIChwcm9jKTsNCn08L1BSRQ0KPjwvUA0KPjxQ DQo+ICAgTm93LCBjb21waWxlIGFuZCBjcmVhdGUgdGhlIGZ1bmN0aW9uOg0K DQo8UFJFDQpDTEFTUz0iUFJPR1JBTUxJU1RJTkciDQo+Y3JlYXRlIGZ1bmN0 aW9uIGV4ZWNxICh0ZXh0LCBpbnQ0KSByZXR1cm5zIGludDQgYXMgJy4uLnBh dGhfdG9fc28nIGxhbmd1YWdlICdjJzs8L1BSRQ0KPg0KDQo8UFJFDQpDTEFT Uz0iUFJPR1JBTUxJU1RJTkciDQo+dmFjPSYjNjI7IHNlbGVjdCBleGVjcSgn Y3JlYXRlIHRhYmxlIGEgKHggaW50NCknLCAwKTsNCmV4ZWNxDQotLS0tLQ0K ICAgIDANCigxIHJvdykNCg0KdmFjPSYjNjI7IGluc2VydCBpbnRvIGEgdmFs dWVzIChleGVjcSgnaW5zZXJ0IGludG8gYSB2YWx1ZXMgKDApJywwKSk7DQpJ TlNFUlQgMTY3NjMxIDENCnZhYz0mIzYyOyBzZWxlY3QgZXhlY3EoJ3NlbGVj dCAqIGZyb20gYScsMCk7DQpOT1RJQ0U6RVhFQ1E6ICAwICYjNjA7JiM2MDsm IzYwOyBpbnNlcnRlZCBieSBleGVjcQ0KDQpOT1RJQ0U6RVhFQ1E6ICAxICYj NjA7JiM2MDsmIzYwOyB2YWx1ZSByZXR1cm5lZCBieSBleGVjcSBhbmQgaW5z ZXJ0ZWQgYnkgdXBwZXIgSU5TRVJUDQoNCmV4ZWNxDQotLS0tLQ0KICAgIDIN CigxIHJvdykNCg0KdmFjPSYjNjI7IHNlbGVjdCBleGVjcSgnaW5zZXJ0IGlu dG8gYSBzZWxlY3QgeCArIDIgZnJvbSBhJywxKTsNCmV4ZWNxDQotLS0tLQ0K ICAgIDENCigxIHJvdykNCg0KdmFjPSYjNjI7IHNlbGVjdCBleGVjcSgnc2Vs ZWN0ICogZnJvbSBhJywgMTApOw0KTk9USUNFOkVYRUNROiAgMCANCg0KTk9U SUNFOkVYRUNROiAgMSANCg0KTk9USUNFOkVYRUNROiAgMiAmIzYwOyYjNjA7 JiM2MDsgMCArIDIsIG9ubHkgb25lIHR1cGxlIGluc2VydGVkIC0gYXMgc3Bl Y2lmaWVkDQoNCmV4ZWNxDQotLS0tLQ0KICAgIDMgICAgICAgICAgICAmIzYw OyYjNjA7JiM2MDsgMTAgaXMgbWF4IHZhbHVlIG9ubHksIDMgaXMgcmVhbCAj IG9mIHR1cGxlcw0KKDEgcm93KQ0KDQp2YWM9JiM2MjsgZGVsZXRlIGZyb20g YTsNCkRFTEVURSAzDQp2YWM9JiM2MjsgaW5zZXJ0IGludG8gYSB2YWx1ZXMg KGV4ZWNxKCdzZWxlY3QgKiBmcm9tIGEnLCAwKSArIDEpOw0KSU5TRVJUIDE2 NzcxMiAxDQp2YWM9JiM2Mjsgc2VsZWN0ICogZnJvbSBhOw0KeA0KLQ0KMSAg ICAgICAgICAgICAgICAmIzYwOyYjNjA7JiM2MDsgbm8gdHVwbGVzIGluIGEg KDApICsgMQ0KKDEgcm93KQ0KDQp2YWM9JiM2MjsgaW5zZXJ0IGludG8gYSB2 YWx1ZXMgKGV4ZWNxKCdzZWxlY3QgKiBmcm9tIGEnLCAwKSArIDEpOw0KTk9U SUNFOkVYRUNROiAgMCANCklOU0VSVCAxNjc3MTMgMQ0KdmFjPSYjNjI7IHNl bGVjdCAqIGZyb20gYTsNCngNCi0NCjENCjIgICAgICAgICAgICAgICAgJiM2 MDsmIzYwOyYjNjA7IHRoZXJlIHdhcyBzaW5nbGUgdHVwbGUgaW4gYSArIDEN CigyIHJvd3MpDQoNCi0tICAgVGhpcyBkZW1vbnN0cmF0ZXMgZGF0YSBjaGFu Z2VzIHZpc2liaWxpdHkgcnVsZToNCg0KdmFjPSYjNjI7IGluc2VydCBpbnRv IGEgc2VsZWN0IGV4ZWNxKCdzZWxlY3QgKiBmcm9tIGEnLCAwKSAqIHggZnJv bSBhOw0KTk9USUNFOkVYRUNROiAgMSANCk5PVElDRTpFWEVDUTogIDIgDQpO T1RJQ0U6RVhFQ1E6ICAxIA0KTk9USUNFOkVYRUNROiAgMiANCk5PVElDRTpF WEVDUTogIDIgDQpJTlNFUlQgMCAyDQp2YWM9JiM2Mjsgc2VsZWN0ICogZnJv bSBhOw0KeA0KLQ0KMQ0KMg0KMiAgICAgICAgICAgICAgICAmIzYwOyYjNjA7 JiM2MDsgMiB0dXBsZXMgKiAxICh4IGluIGZpcnN0IHR1cGxlKQ0KNiAgICAg ICAgICAgICAgICAmIzYwOyYjNjA7JiM2MDsgMyB0dXBsZXMgKDIgKyAxIGp1 c3QgaW5zZXJ0ZWQpICogMiAoeCBpbiBzZWNvbmQgdHVwbGUpDQooNCByb3dz KSAgICAgICAgICAgICBeXl5eXl5eXiANCiAgICAgICAgICAgICAgICAgICAg IHR1cGxlcyB2aXNpYmxlIHRvIGV4ZWNxKCkgaW4gZGlmZmVyZW50IGludm9j YXRpb25zPC9QUkUNCj48L1ANCj48RElWDQpDTEFTUz0iTkFWRk9PVEVSIg0K PjxIUg0KQUxJR049IkxFRlQiDQpXSURUSD0iMTAwJSI+PFRBQkxFDQpXSURU SD0iMTAwJSINCkJPUkRFUj0iMCINCkNFTExQQURESU5HPSIwIg0KQ0VMTFNQ QUNJTkc9IjAiDQo+PFRSDQo+PFREDQpXSURUSD0iMzMlIg0KQUxJR049Imxl ZnQiDQpWQUxJR049InRvcCINCj48QQ0KSFJFRj0ic3BpMjE1NDMuaHRtIg0K PlByZXY8L0ENCj48L1REDQo+PFREDQpXSURUSD0iMzQlIg0KQUxJR049ImNl bnRlciINClZBTElHTj0idG9wIg0KPjxBDQpIUkVGPSJwb3N0Z3Jlcy5odG0i DQo+SG9tZTwvQQ0KPjwvVEQNCj48VEQNCldJRFRIPSIzMyUiDQpBTElHTj0i cmlnaHQiDQpWQUxJR049InRvcCINCj48QQ0KSFJFRj0ieHBsYW5nLmh0bSIN Cj5OZXh0PC9BDQo+PC9URA0KPjwvVFINCj48VFINCj48VEQNCldJRFRIPSIz MyUiDQpBTElHTj0ibGVmdCINClZBTElHTj0idG9wIg0KPlZpc2liaWxpdHkg b2YgRGF0YSBDaGFuZ2VzPC9URA0KPjxURA0KV0lEVEg9IjM0JSINCkFMSUdO PSJjZW50ZXIiDQpWQUxJR049InRvcCINCj48QQ0KSFJFRj0ic3BpLmh0bSIN Cj5VcDwvQQ0KPjwvVEQNCj48VEQNCldJRFRIPSIzMyUiDQpBTElHTj0icmln aHQiDQpWQUxJR049InRvcCINCj5Qcm9jZWR1cmFsIExhbmd1YWdlczwvVEQN Cj48L1RSDQo+PC9UQUJMRQ0KPjwvRElWDQo+PC9CT0RZDQo+PC9IVE1MDQo+ ---559023410-851401618-1000775509=:16528--