pgsql.pgsql 2.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118
  1. BEGIN;
  2. /**
  3. * Samples from PostgreSQL src/tutorial/basics.source
  4. */
  5. CREATE TABLE weather (
  6. city varchar(80),
  7. temp_lo int, -- low temperature
  8. temp_hi int, -- high temperature
  9. prcp real, -- precipitation
  10. "date" date
  11. );
  12. CREATE TABLE cities (
  13. name varchar(80),
  14. location point
  15. );
  16. INSERT INTO weather
  17. VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
  18. INSERT INTO cities
  19. VALUES ('San Francisco', '(-194.0, 53.0)');
  20. INSERT INTO weather (city, temp_lo, temp_hi, prcp, "date")
  21. VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
  22. INSERT INTO weather (date, city, temp_hi, temp_lo)
  23. VALUES ('1994-11-29', 'Hayward', 54, 37);
  24. SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, "date" FROM weather;
  25. SELECT city, temp_lo, temp_hi, prcp, "date", location
  26. FROM weather, cities
  27. WHERE city = name;
  28. /**
  29. * Dollar quotes starting at the end of the line are colored as SQL unless
  30. * a special language tag is used. Dollar quote syntax coloring is implemented
  31. * for Perl, Python, JavaScript, and Json.
  32. */
  33. create or replace function blob_content_chunked(
  34. in p_data bytea,
  35. in p_chunk integer)
  36. returns setof bytea as $$
  37. -- Still SQL comments
  38. declare
  39. v_size integer = octet_length(p_data);
  40. begin
  41. for i in 1..v_size by p_chunk loop
  42. return next substring(p_data from i for p_chunk);
  43. end loop;
  44. end;
  45. $$ language plpgsql stable;
  46. -- pl/perl
  47. CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $perl$
  48. # perl comment...
  49. my ($x,$y) = @_;
  50. if (! defined $x) {
  51. if (! defined $y) { return undef; }
  52. return $y;
  53. }
  54. if (! defined $y) { return $x; }
  55. if ($x > $y) { return $x; }
  56. return $y;
  57. $perl$ LANGUAGE plperl;
  58. -- pl/python
  59. CREATE FUNCTION usesavedplan() RETURNS trigger AS $python$
  60. # python comment...
  61. if SD.has_key("plan"):
  62. plan = SD["plan"]
  63. else:
  64. plan = plpy.prepare("SELECT 1")
  65. SD["plan"] = plan
  66. $python$ LANGUAGE plpythonu;
  67. -- pl/v8 (javascript)
  68. CREATE FUNCTION plv8_test(keys text[], vals text[]) RETURNS text AS $javascript$
  69. var o = {};
  70. for(var i=0; i<keys.length; i++){
  71. o[keys[i]] = vals[i];
  72. }
  73. return JSON.stringify(o);
  74. $javascript$ LANGUAGE plv8 IMMUTABLE STRICT;
  75. -- json
  76. select * from json_object_keys($json$
  77. {
  78. "f1": 5,
  79. "f2": "test",
  80. "f3": {}
  81. }
  82. $json$);
  83. -- psql commands
  84. \df cash*
  85. -- Some string samples.
  86. select 'don''t do it now;' || 'maybe later';
  87. select E'dont\'t do it';
  88. select length('some other''s stuff' || $$cat in hat's stuff $$);
  89. select $$ strings
  90. over multiple
  91. lines - use dollar quotes
  92. $$;
  93. END;