reorganizing notes

1 files changed, 57 insertions, 0 deletions

diff --git a/examples/sql/pg_function.sql b/examples/sql/pg_function.sql
new file mode 100644
index 0000000..5edbb26
--- /dev/null
+++ b/examples/sql/pg_function.sql
@@ -0,0 +1,57 @@
+
+/*
+ * Source: https://stackoverflow.com/questions/21087710/how-to-write-update-function-stored-procedure-in-postgresql
+ */
+CREATE OR REPLACE FUNCTION update_user(
+    nickname   LABEL,
+    full_name  TEXT   DEFAULT NULL,
+    email      EMAIL  DEFAULT NULL,
+    uri        URI    DEFAULT NULL,
+    twitter    CITEXT DEFAULT NULL
+) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
+/*
+    % SELECT update_user(
+        nickname  := 'theory',
+        full_name := 'David E. Wheeler',
+        email     := 'justatheory@pgxn.org',
+        uri       := 'http://www.justatheory.com/',
+        twitter   :- 'theory'
+    );
+     update_user 
+    ─────────────
+     t
+
+Update the specified user. The user must be active. The nickname cannot be
+changed. The password can only be changed via `change_password()` or
+`reset_password()`. Pass other attributes as:
+
+full_name
+: The full name of the user.
+
+email
+: The email address of the user. Must be a valid email address as verified by
+  [Email::Valid](http://search.cpan.org/perldoc?Email::Valid).
+
+uri
+: Optional URI for the user. Should be a valid URI as verified by
+  [Data::Validate::URI](http://search.cpan.org/perldoc?Data::Validate::URI).
+
+twitter
+: Optional Twitter username. A leading "@" wil be removed.
+
+Returns true if the user was updated, and false if not.
+
+*/
+BEGIN
+    UPDATE users
+       SET full_name      = COALESCE(update_user.full_name, users.full_name),
+           email          = COALESCE(update_user.email,     users.email),
+           uri            = COALESCE(update_user.uri,       users.uri),
+           twitter        = COALESCE(trim(leading '@' FROM update_user.twitter), users.twitter),
+           updated_at     = NOW()
+     WHERE users.nickname = update_user.nickname
+       AND users.status   = 'active';
+    RETURN FOUND;
+END;
+$$;
+