badri

CREATE OR REPLACE FUNCTION test_func(data json) RETURNS json AS $$
  return JSON.stringify(data);
$$ LANGUAGE PLV8;

SELECT test_func('{"a": {"b":"foo"}}'::json);
     test_func     
-------------------
 {"a":{"b":"foo"}}

badri

[
  {
    "id": 1,
    "name": "Arts & Photography",
    "children": [
      {
    "id": 2,
    "name": "Architecture",
    "children": [

badri

CREATE OR REPLACE FUNCTION get_children(genre_id integer)
RETURNS json AS $$
DECLARE
result json;
BEGIN
SELECT array_to_json(array_agg(row_to_json(t))) INTO result -- inject output into result variable
FROM ( -- same CTE as above
  WITH RECURSIVE genres_materialized_path AS (
    SELECT id, name, ARRAY[]::INTEGER[] AS path
    FROM genres WHERE parent_id IS NULL

badri

 id |        name        |  path   
----+--------------------+---------
 16 | Hard Boiled        | {14,15}
 17 | Police Procedurals | {14,15}
(2 rows)

badri

WITH RECURSIVE genres_materialized_path AS (
  SELECT id, name, ARRAY[]::INTEGER[] AS path
  FROM genres WHERE parent_id IS NULL

  UNION ALL

  SELECT genres.id, genres.name, genres_materialized_path.path || genres.parent_id
  FROM genres, genres_materialized_path
  WHERE genres.parent_id = genres_materialized_path.id
) SELECT * FROM genres_materialized_path WHERE 15 = genres_materialized_path.path[array_upper(genres_materialized_path.path,1)];

badri

SELECT ARRAY[4,5,6] || 7 ;

 ?column?  
-----------
 {4,5,6,7}

badri

WITH RECURSIVE genres_materialized_path AS (
  SELECT id, name, ARRAY[]::INTEGER[] AS path
  FROM genres WHERE parent_id IS NULL

  UNION ALL

  SELECT genres.id, genres.name, genres_materialized_path.path || genres.parent_id
  FROM genres, genres_materialized_path
  WHERE genres.parent_id = genres_materialized_path.id
) SELECT * FROM genres_materialized_path;

badri

INSERT INTO genres (name, parent_id) VALUES ('Arts & Photography',NULL);
INSERT INTO genres (name, parent_id) VALUES ('Architecture',1);
INSERT INTO genres (name, parent_id) VALUES ('Graphic Design',1);
INSERT INTO genres (name, parent_id) VALUES ('Music',1); -- 4
INSERT INTO genres (name, parent_id) VALUES ('Songbooks',4);
INSERT INTO genres (name, parent_id) VALUES ('Instruments & Performers',4); -- 6
INSERT INTO genres (name, parent_id) VALUES ('Brass',6);
INSERT INTO genres (name, parent_id) VALUES ('Woodwinds',6);

INSERT INTO genres (name, parent_id) VALUES ('Comics & Graphic Novels', NULL); -- 9

badri

CREATE TABLE genres (
    id serial NOT NULL PRIMARY KEY,
    name character varying(255) NOT NULL,
    parent_id integer REFERENCES genres (id)
);

badri

# github.com/axelerant/tsuru/app
app/app.go:98: cannot use app (type *App) as type provision.App in argument to Provisioner.Units:
	*App does not implement provision.App (wrong type for Run method)
		have Run(string, "io".Writer, bool, bool) error
		want Run(string, "io".Writer, bool) error
app/app.go:346: cannot use app (type *App) as type provision.App in argument to Provisioner.Destroy:
	*App does not implement provision.App (wrong type for Run method)
		have Run(string, "io".Writer, bool, bool) error
		want Run(string, "io".Writer, bool) error
app/app.go:455: cannot use app (type *App) as type provision.App in argument to Provisioner.RemoveUnits: