chanmix51

<?php

use Pomm\Connection\Database;

$loader = require __DIR__."/vendor/.composer/autoload.php";
$loader->add('YourDb', __DIR__."/model");

$database = new Database(array('dsn' => 'pgsql://user:pass@host:port/your_db'));

$book = $database

chanmix51

CREATE DOMAIN latlong AS point CHECK (VALUE[0] BETWEEN -90.0 AND 90.0 AND VALUE[1] BETWEEN -180 AND 180);

CREATE OR REPLACE FUNCTION orthodromic_distance(latlong, latlong) RETURNS float AS $_$
     SELECT acos(
              sin(radians($1[0])) 
            * 
              sin(radians($2[0]))
            + 
              cos(radians($1[0])) 
            * 

chanmix51

(9.1/greg) test_db=> SELECT hstore(clients) FROM clients;
                                                      hstore
------------------------------------------------------------------------------------------------------------------
 "times"=>NULL, "business"=>"Dewey, Cheetham, and Howe", "client_id"=>"DCH", "contact_name"=>"John Dewey"
 "times"=>NULL, "business"=>"Investigators at Your Service", "client_id"=>"IYS", "contact_name"=>"Spencer Tracey"
 "times"=>NULL, "business"=>"It is all about ABC", "client_id"=>"ABC", "contact_name"=>"Abby Back"
(3 rows)

chanmix51

<?php

namespace YourDb\PublicSchema;

use YourDb\PublicSchema\Base\SsObjectMap as BaseSsObjectMap;
use YourDb\PublicSchema\SsObject;
use \Pomm\Exception\Exception;
use \Pomm\Query\Where;

class SsObjectMap extends BaseSsObjectMap

chanmix51

BEGIN;
-- is_email
-- Check if the given string is a valid email format or not
-- @param VARCHAR email the string to check
-- @return BOOLEAN 
CREATE OR REPLACE FUNCTION is_email(email VARCHAR) RETURNS BOOLEAN AS $$
BEGIN
      RETURN email ~* e'^([^@\\s]+)@((?:[a-z0-9-]+\\.)+[a-z]{2,})$';
END;
$$ LANGUAGE plpgsql;

chanmix51

SELECT author FROM author;
|      author       |
+-------------------+
| "(1,'john doe')"  |
+-------------------+
| "(2,'Edgar')"     |
+-------------------+

SELECT 
  author.id, 

chanmix51

ALTER TABLE city ADD COLUMN last_weather_data weather_probe;
UPDATE 
  city c 
SET 
  last_weather = wp 
FROM 
  (
    SELECT 
      wp.city_name,
      wp.created_at,

chanmix51

SELECT 
  city_name, 
  created_at,
  CAST(array_to_string(xpath('/xml_api_reply/weather/current_conditions/temp_c/@data', xml_response), '') AS integer) AS temperature,
  substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/wind_condition/@data', xml_response), '') FROM '% #"[NSOE]+#" %' FOR '#') AS wind_direction,
  CAST(substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/wind_condition/@data', xml_response), '') FROM '% #"[0-9]+#"%' FOR '#') AS integer) AS wind_speed,
  CAST(substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/humidity/@data', xml_response), '') FROM '% #"[0-9]+#"%' FOR '#') AS integer) AS humidity,
  array_to_string(xpath('/xml_api_reply/weather/current_conditions/icon/@data', xml_response), '') AS condition
FROM 
  probe_xml 

chanmix51

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

chanmix51

<?php

function help()
{
    printf("Get the stored procedures source code.\n");
    printf("Usage:\n");
    printf("show_sp_source schema name\n");
    printf("\nname is the stored procedure's name.\n");
    printf("schema is the database schema.\n");
    printf("environment set the database connection that will be used (default: dev).\n");