Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nKNUE-0002Pu-Ky for pgsql-sql@arkaria.postgresql.org; Wed, 16 Feb 2022 16:49:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nKNUD-0002JQ-KU for pgsql-sql@arkaria.postgresql.org; Wed, 16 Feb 2022 16:49:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nKAjj-0007Kn-GU for pgsql-sql@lists.postgresql.org; Wed, 16 Feb 2022 03:12:11 +0000 Received: from mxout26.s.uw.edu ([140.142.234.176]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nKAjd-0002OQ-5W for pgsql-sql@lists.postgresql.org; Wed, 16 Feb 2022 03:12:07 +0000 Received: from mail-pj1-f72.google.com (mail-pj1-f72.google.com [209.85.216.72]) by mxout26.s.uw.edu (8.14.4+UW20.07/8.14.4+UW19.10) with ESMTP id 21G38oXf006589 (version=TLSv1/SSLv3 cipher=AES128-GCM-SHA256 bits=128 verify=OK) for ; Tue, 15 Feb 2022 19:08:50 -0800 Received: by mail-pj1-f72.google.com with SMTP id a15-20020a17090ad80f00b001b8a1e1da50so685685pjv.6 for ; Tue, 15 Feb 2022 19:08:50 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=8pNJzSSHCgtPRJ2WDLF9rjbTta1xJc2KE4cmPW3iQH8=; b=w82XiatJ+Yy7DrAras5ywSJCciTlXJQyWc68YI6YhM6A8nWxmYb+4hW0ic/N75Lp/F 1oSSVwYyKoheG8oE5+fqirIFbqtJq74Bh9/6qbxuZ3IhzDN4vOg3l8QS6k1v8uqj93fQ UdoXmz88lRC5RAatdrCBgxbzS9neuL8Fyc8OeoB28uWKbdhfOw/BwCud6dZiWG4JQGV9 befZZmCIIW7DrnOrEFl3VGiwZKldMMPwy87/l76X+VsSBKfygj64SIijUFzJUmq8Ahny 60lWhT7y5AMm0ku8mve8yW5lvTzAU8B10CmD23yBxLgnhsHvb3vI/KfjdXzOuWhM/sV8 3u7Q== X-Gm-Message-State: AOAM530erNxkhMhhFom2XTyaOfIo+o7dBYoK21utWK07oKwcfdprKF7D sxiepGQb8EO32uSpKFk9yRk6dRuqVfVHUEhRVoOEUGOiqRE0BD9Z02W4potPRCeyr5aWnnlx+6l s4R/6dJGVA/km5vC9iKx9zUnG/aTLTgwwbRs43IrRxHP2v9tmhyTY X-Received: by 2002:a17:90a:ba8d:b0:1b9:2de6:129 with SMTP id t13-20020a17090aba8d00b001b92de60129mr7825690pjr.97.1644980929574; Tue, 15 Feb 2022 19:08:49 -0800 (PST) X-Google-Smtp-Source: ABdhPJySQxKgCEmFthMBeHQ67jHeqvKFdnDbliljE2LUGZ/vxKsI3cdqhGGnREMBWi09RvdbCkqKmA== X-Received: by 2002:a17:90a:ba8d:b0:1b9:2de6:129 with SMTP id t13-20020a17090aba8d00b001b92de60129mr7825651pjr.97.1644980929080; Tue, 15 Feb 2022 19:08:49 -0800 (PST) Received: from hithlum ([2601:602:9e80:d340::562c]) by smtp.gmail.com with ESMTPSA id lp10sm11234238pjb.44.2022.02.15.19.08.47 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 15 Feb 2022 19:08:48 -0800 (PST) Date: Tue, 15 Feb 2022 19:08:46 -0800 From: Skylar Thompson To: pgsql-sql@lists.postgresql.org Subject: Re: Try catch error, return data Message-ID: <20220216030846.v7g4ggin5om7xwpu@hithlum> X-Accept-Primary-Language: en X-Accept-Secondary-Language: es References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-PMX-Version: 6.4.9.2830568, Antispam-Engine: 2.7.2.2107409, Antispam-Data: 2022.2.16.25715, AntiVirus-Engine: 5.87.0, AntiVirus-Data: 2021.12.11.5870000 X-PMX-Server: mxout26.s.uw.edu X-Sophos-SenderHistory: ip=209.85.216.72,fs=61454,da=121429734,mc=3121,sc=4,hc=3117,sp=0,fso=71226550,re=145,sd=0,hd=30 X-Uwash-Spam: Gauge=IIIIIIII, Probability=8%, Report= HTML_00_01 0.05, HTML_00_10 0.05, BODYTEXTP_SIZE_3000_LESS 0, BODY_SIZE_1000_LESS 0, BODY_SIZE_2000_LESS 0, BODY_SIZE_5000_LESS 0, BODY_SIZE_7000_LESS 0, BODY_SIZE_700_799 0, DATE_TZ_NA 0, DQ_S_H 0, FROM_EDU_TLD 0, INVALID_MSGID_NO_FQDN 0, IN_REP_TO 0, KNOWN_MTA_TFX 0, LEGITIMATE_SIGNS 0, MSG_THREAD 0, NO_FUR_HEADER 0, REFERENCES 0, SENDER_NO_AUTH 0, SPF_PASS 0, SXL_IP_TFX_WM 0, URI_ENDS_IN_HTML 0, URI_WITH_PATH_ONLY 0, WEBMAIL_SOURCE 0, __ANY_URI 0, __BODY_NO_MAILTO 0, __BOUNCE_CHALLENGE_SUBJ 0, __BOUNCE_NDR_SUBJ_EXEMPT 0, __CD 0, __CP_URI_IN_BODY 0, __CT 0, __CT_TEXT_PLAIN 0, __DQ_IP_FSO_LARGE 0, __DQ_IP_SUSP_3 0, __DQ_NEG_HEUR 0, __DQ_NEG_IP 0, __DQ_S_HIST_1 0, __DQ_S_IP_100K 0, __DQ_S_IP_1MO 0, __DQ_S_IP_2D 0, __DQ_S_IP_MC_100_P 0, __DQ_S_IP_MC_10_P 0, __DQ_S_IP_MC_1K_P 0, __DQ_S_IP_MC_1_P 0, __DQ_S_IP_MC_5_P 0, __DQ_S_IP_RE_100_P 0, __DQ_S_IP_SC_1_P 0, __DQ_S_IP_SP_0_P 0, __FORWARDED_MSG 0, __FROM_DOMAIN_NOT_IN_BODY 0, __FUR_RDNS_GMAIL 0, __HAS_FROM 0, __HAS_MSGID 0, __HAS_REFERENCES 0, __HELO_GMAIL 0, __HTTPS_URI 0, __IN_REP_TO 0, __MAIL_CHAIN 0, __MIME_TEXT_ONLY 0, __MIME_TEXT_P 0, __MIME_TEXT_P1 0, __MIME_VERSION 0, __MSGID_DOMAIN_NOT_IN_HDRS 0, __MULTIPLE_URI_TEXT 0, __NO_HTML_TAG_RAW 0, __PHISH_SPEAR_STRUCTURE_1 0, __PHISH_SPEAR_SUBJ_ALERT 0, __RDNS_WEBMAIL 0, __REFERENCES 0, __SANE_MSGID 0, __SUBJ_ALPHA_END 0, __SUBJ_ALPHA_NEGATE 0, __SUBJ_REPLY 0, __TO_MALFORMED_2 0, __TO_NO_NAME 0, __URI_IN_BODY 0, __URI_NOT_IMG 0, __URI_NO_MAILTO 0, __URI_NS , __URI_WITH_PATH 0, __X_GM_MESSAGE_STATE 0, __X_GOOGLE_DKIM_SIGNATURE 0, __X_GOOGLE_SMTP_SOURCE 0, __YOUTUBE_RCVD 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I think you're looking for EXCEPTION: https://www.postgresql.org/docs/14/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING At the point you've trapped the error, you can then return something based on the error. You can find a list of exception codes in one of the appendices: https://www.postgresql.org/docs/14/errcodes-appendix.html On Wed, Feb 16, 2022 at 02:13:05AM +0000, Shaozhong SHI wrote: > In Python, one can use try statement to catch error, error can be stored as > data. > > Can plpgsql catch and return error as data or a data table? > > Regards, David -- -- Skylar Thompson (skylar2@u.washington.edu) -- Genome Sciences Department (UW Medicine), System Administrator -- Foege Building S046, (206)-685-7354 -- Pronouns: He/Him/His