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 1qsmYh-009MNS-UL for pgsql-sql@arkaria.postgresql.org; Tue, 17 Oct 2023 16:04:39 +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 1qsmYf-00BcEX-Uf for pgsql-sql@arkaria.postgresql.org; Tue, 17 Oct 2023 16:04:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qsmYf-00BcE9-Jg for pgsql-sql@lists.postgresql.org; Tue, 17 Oct 2023 16:04:38 +0000 Received: from mail-oo1-xc31.google.com ([2607:f8b0:4864:20::c31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qsmYc-001CcJ-AK for pgsql-sql@lists.postgresql.org; Tue, 17 Oct 2023 16:04:37 +0000 Received: by mail-oo1-xc31.google.com with SMTP id 006d021491bc7-581b6b93bd1so1370546eaf.1 for ; Tue, 17 Oct 2023 09:04:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697558673; x=1698163473; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=mpHKDkIza5MjdU/eHnSe5mbrtW32xMjx82Qzx7a85gs=; b=mL0wgS2Cxr5RWFtszXKO0YhX6qa8tVwrgfChc5ly7UYTEgfIq+LUQYj5FY6qnNo47w KGn8x7q4AFtEoccR1yNogOVSOR7po2AcLT0dP5f4xtcxWx1oTnhJyGdzYAcWMMtLWh/U oTCeh2k+nFDkB604J1c4nPRwiDmrgS0Ruq13ggwiHElbJ+29KbASWviYYt9ngs5Yk5pt Xs7s3pOv31o4W5f3dVU1r4T7Fjs20sSgYXk7J+bv/pxvFpCTwSTfmjhFcOEfVLHRSi3l Umtz1fbD/x4HKkKJEZVIhGRTA/mUwQnTR9n5TDpmK8s4fxgUj1+6BANZTWc0b7VTxciA u4Eg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697558673; x=1698163473; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=mpHKDkIza5MjdU/eHnSe5mbrtW32xMjx82Qzx7a85gs=; b=qrbXZ2uaOBY+pZuyNE/x/p7eN7K2AxEtCgQgqVFKONyHJph2S+HZGVlB+3ZCDpgVde R9aKdy1eJxc39OcXnjeboR19ulkhL8Ipm5v0nP3NIa8qzHyu2HZtCLiKIhAUkmPzc7wh zZyNOLSWQ6iCpl1I9uVNKy+cRSDO47lg1q/oH84uNMjzWZFU+EilNoVFh7/oJyWWtN7c eU9kILANngMNhLqL830GxM7tgKbQYHWt37EMCcvNJSg37tq4ixnNoYqXb1CazPnTLKZL TgZ9oVBXWjvwxNWXPUOYslbZa73+LYG+yd4Tjy0caB4ApReE/xyj3uBq7wmwisWurbAZ WlOw== X-Gm-Message-State: AOJu0YxmD2b7zDLXNQ3VxKkb3QTZ/F2zgtAC2tiSUzquh+J7kiV51iaO TaiIL369whkrc3LAVZ4SDDxO0RHY+7qxqtJqlDzI6gC2 X-Google-Smtp-Source: AGHT+IHc8tlWLjWKSoTFpzC5ubXkrqJ7cxM4dhi1LJjwNGXmvggvv7T0GGUGeFb/FEBJRJE7iiMYFuVh1SxpIW2ZxpQ= X-Received: by 2002:a4a:d198:0:b0:581:e8c8:f7f9 with SMTP id j24-20020a4ad198000000b00581e8c8f7f9mr1002332oor.9.1697558673459; Tue, 17 Oct 2023 09:04:33 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 17 Oct 2023 09:04:16 -0700 Message-ID: Subject: Re: stored procedures To: Shaozhong SHI Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000b78c9b0607ebad77" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b78c9b0607ebad77 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Oct 17, 2023 at 8:54=E2=80=AFAM Shaozhong SHI wrote: > How easily turn do statement scripts into stored procedures? Much > modification is needed? > > What is the advantage to do so? > Depends on the script but possibly a matter of copy-paste for the simplest. Advantages: The script is on the server, can require permissions, has a name (I suppose a script file does too...). Easier to deal with input parameters. Fewer components involved. Usable by components that can't issue SQL directly - e.g., GraphQL mutations. Disadvantages: Changing the script is now a formal migration for the database instead of an application update. David J. --000000000000b78c9b0607ebad77 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Oct 17, 2023 at 8:54=E2=80=AFAM Shaozhong SHI <= shishaozhong@gmail.com> wr= ote:
How easily turn do statement = scripts into stored procedures?=C2=A0 Much modification is needed?

=
What is the advantage to do so?
Depends on the script but possibly a matter of copy-paste for the= simplest.

Advantages: The script is on the server, ca= n require permissions, has a name (I suppose a script file does too...).=C2= =A0 Easier to deal with input parameters.=C2=A0 Fewer components involved.= =C2=A0 Usable by components that can't issue SQL directly - e.g., Graph= QL mutations.

Disadvantages: Changing the script is no= w a formal migration for the database instead of an application update.

David J.

--000000000000b78c9b0607ebad77--