Databases
StackOverflow2013
Postsdbo
POmacro-like function as filter in postgresql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmacro-like function as filter in postgresql
    primarykey
    data
    text
    <p>I have a table with some RDF statements inside, like <code>Quads(graph, subject, verb, object)</code> and I find myself doing queries like:</p> <pre><code>select * from quads where verb = 'rdf:type' and object = 'smtg:Type' select * from quads where verb = 'rdf:label' and object = 'bla bla' </code></pre> <p>I would like to express this in a form like</p> <pre><code>select * from quads where type('smtg:Type') select * from quads where label('bla bla') </code></pre> <p>While this seems a "trivial" text substitution, I have no idea of how and if this would be implementable in postgresql, though I sort of expect this to be impossible. </p> <p>To the best of my knowledge I can use a function to hardcode the constants, and do</p> <pre><code>select * from quads where rdftype() = verb and object = 'smtg:Type' </code></pre> <p>or I can use a function as the <code>from</code> argument like</p> <pre><code>select * from typed('smtg:Type') </code></pre> <p>sadly, the former is quite verbose, and the latter does not seem to be amendable to combination e.g. I can't do an hypothetical</p> <pre><code>select * from quads where type('smtg:Type') and inGraph('mygraph') </code></pre> <p>Is there a way to do what I want? It seems impossible, but I wonder if there is a way.</p> <p><strong>EDIT</strong>: a sqlfiddle instance: <a href="http://sqlfiddle.com/#!1/40b2c/3" rel="nofollow">http://sqlfiddle.com/#!1/40b2c/3</a> . </p> <p>To be more clear, the reason why this seems unfeasible is that the macro-ish function would be returning a boolean (it is used in the <code>where</code> clause, as if it where a <code>select $1=somevalue</code>) but using an "implicit" row argument in which it would access multiple fields at once, and I have not seen such an example in the pg docs. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USriffraff
    1. USriffraff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI don't see a reason why this couldn't be done in a PL/pgSQL function. But to be sure we would need more information. Please post the exact table structure (as `create table...`) and some sample data (as `insert into ...`) you might post a working copy with your first two statements at http://sqlfiddle.com
      singulars
      1. POmacro-like function as filter in postgresql
      1. USa_horse_with_no_name
    2. COIf `verb` is functionally dependent on `object`, then you should remove the redundant column and normalize your schema. The additional condition on `verb` would be redundant next to the one on `object`. If `verb` can *not* be deduced from `object`, then it is still unclear how you resolve `type('#Person')`. Generally, you can do almost *anything* in plpgsql. When you run into an problem that cannot be solved with plain SQL, generate *dynamic SQL* inside a plpgsql function. Search for `[plpgsql] EXECUTE` or `dynamic-sql` here on SO for more.
      singulars
      1. POmacro-like function as filter in postgresql
      1. USErwin Brandstetter
    3. CO`verb` is not functionally dependent on object. I don't understand what you mean by "resolve", I'm sorry, but, `type('#Person')` would be equivalent to a `verb_and_object_equals_to('type', '#Person')`with one argument fixed.
      singulars
      1. POmacro-like function as filter in postgresql
      1. USriffraff
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload