In the last post, we have seen how we can do complex queries on the PicoLisp Database with help of Pilog.

Now we will see an alternative way that may be more convenient for same cases: The select function with a similar syntax to the SQL language.

Update January 11, 2026:

select has been replaced by the search function since pil21-release 23.5.30. The blogpost content has been udpated accordingly. You can find more information on search in thePicoLisp Reference.

If you still want to use the previous select function, you can still load it separately by loading it with (load “@lib/select.l”). The updated script with search instead of select can be found here.

About SQL

SQL stands for "Structured Query Language" and belongs to the family of declarative languages (like functional and logical programming). It is mainly used for relational database management systems. Since relational databases are so common, the SQL syntax is quite well-known and easier to use than the Pilog predicates.

For this reason, PicoLisp offers an interactive database function loosely modelled after the SQL "SELECT" command. It is basically a front-end to the Pilog select/3 predicate that we discussed in the previous post. It is only intended for interactive use (debugging) and less powerful than the original pilog predicate. "Interactive use" means you can only use it in the REPL but you can't call it in a program.

Basic Syntax

Let's open a database and start the REPL, for example our family.l example:

$ pil family-original-tutorial.l -family~main +
family:

Now let's reproduce the SQL statement SELECT * FROM Person, which steps through the whole database:

# SELECT * FROM Person

family: (select +Person)
{A64} (+Man)
   nm "Adalbert Ferdinand Berengar Viktor of Prussia"
   ma {A57}
   fin 711698
   dat 688253
   pa {A55}

{A127} (+Man)
   nm "Adam"
   mate {A130}

{A115} (+Man)
   nm "Albert Edward"
   kids ({A116} {A117} {A120} {A121} {A122} {A123} {A124} {A54} {A46})
   job "Prince"
   mate {A53}
   fin 680370
   dat 664554

Note: The database content is defined in the "family"-Folder of the git repository. More on how to open databases can be found in this post.

Or adding a WHERE clause:

# SELECT nm, dat FROM Person WHERE nm LIKE "%Edward%"

family: (select nm dat +Person nm "Edward")
"Edward" "1964-03-10" {A13}
"George Edward" NIL {A35}
"Albert Edward" "1819-08-26" {A115}

As you can see, the LIKE operator and the % wildcard symbol are not needed because PicoLisp already indexes all substrings as well.

Now a more complicated one: Find the partner of a king called "Edward".

# SELECT dat, fin, p1.nm, p2.nm
#    FROM Person p1, Person p2
#    WHERE p1.nm LIKE "%Edward%"
#    AND p1.job LIKE "King%"
#    AND p1.mate = p2.mate  -- Actually, in a SQL model we'd need
#                           -- another table here for the join

In PicoLisp:

: (select dat fin nm (mate nm) +Person nm "Edward" job "King")

Update 2024: Due to changes in the PicoLisp database search mechanism, the query in recent PicoLisp versions is as follows:

: (select dat fin nm (: mate nm) +Person nm "Edward" job "King")

We get two hits:

family: (select dat fin nm (mate nm) +Person nm "Edward" job "King")
"1894-06-23" "1972-05-28" "Edward VIII" "Wallace Simpson" {A37}
"1841-11-09" NIL "Edward VII" "Alexandra of Denmark" {A46}

In the tutorials for older versions of PicoLisp, you can also find an entry about the update function. Unfortunately, this feature is not available in pil21 anymore.

In the next post, we will see how we can extend our PicoLisp GUI by a more powerful search function with help of Pilog.

Sources

PicoLisp tutorial

"Poor Man's SQL": Accessing the PicoLisp Database with SELECT

Comments (1)

The PicoLisp Database

How to Write Database Queries with Pilog

More from this blog

A Railroad Simulation with DES

PicoLisp Explored: Discrete Event Simulation

PicoLixp Explored: On Coroutines

Data Visualization with D3.js and PicoLisp

Improving User Experience with Server-Sent Events

About SQL

Basic Syntax

Sources

Command Palette

Comments (1)

The PicoLisp Database

How to Write Database Queries with Pilog

More from this blog

About SQL

Basic Syntax

Sources